/**
 * 文件名：OrderFacadeImpl.java
 *
 * 创建人：Fyh - 1009173473@qq.com
 *
 * 创建时间：2019年4月16日 下午3:02:17
 *
 * 版权所有：南京植百汇智能科技服务有限公司
 */
package com.z100h.supplychain.order.facade.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jeeplus.common.config.Global;
import com.jeeplus.core.persistence.Page;
import com.z100h.supplychain.evidence.entity.ErpResPrinter;
import com.z100h.supplychain.evidence.entity.StockoutSku;
import com.z100h.supplychain.evidence.service.StockoutErpResourceService;
import com.z100h.supplychain.evidence.service.StockoutSkuService;
import com.z100h.supplychain.order.entity.ErpSkuDetail;
import com.z100h.supplychain.order.entity.ErpSpecSku;
import com.z100h.supplychain.order.entity.OrderCustomer;
import com.z100h.supplychain.order.entity.OrderDetailSummary;
import com.z100h.supplychain.order.entity.OrderDetailSummarySpec;
import com.z100h.supplychain.order.entity.OrderDetailSummarySpecConsist;
import com.z100h.supplychain.order.entity.OrderDetailVo;
import com.z100h.supplychain.order.entity.OrderSpec;
import com.z100h.supplychain.order.entity.OrderStorage;
import com.z100h.supplychain.order.entity.OrderVo;
import com.z100h.supplychain.order.facade.OrderFacade;
import com.z100h.supplychain.order.service.OrderCustomerService;
import com.z100h.supplychain.order.service.OrderDetailSummaryService;
import com.z100h.supplychain.order.service.OrderDetailSummarySpecConsistService;
import com.z100h.supplychain.order.service.OrderDetailSummarySpecService;
import com.z100h.supplychain.order.service.OrderService;
import com.z100h.supplychain.order.service.OrderSpecService;
import com.z100h.supplychain.order.service.OrderStorageService;

/**
 * [描述信息：说明类的基本功能]
 *
 * @author Fyh - 1009173473@qq.com
 * @version 1.0 Created on 2019年4月16日 下午3:02:17
 */
@Service
public class OrderFacadeImpl implements OrderFacade {

	@Autowired
	private OrderCustomerService orderCustomerService;

	@Autowired
	private OrderStorageService orderStorageService;

	@Autowired
	private OrderSpecService orderSpecService;

	@Autowired
	private OrderDetailSummaryService orderDetailSummaryService;

	@Autowired
	private OrderDetailSummarySpecService orderDetailSummarySpecService;

	@Autowired
	private OrderDetailSummarySpecConsistService orderDetailSummarySpecConsistService;

	@Autowired
	private StockoutSkuService stockoutSkuService;

	@Autowired
	private OrderService orderService;

	@Autowired
	private StockoutErpResourceService stockoutErpResourceService;

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#getOrderCustomerByOrderId(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年4月16日 下午3:03:30
	 */
	@Override
	public OrderCustomer getOrderCustomerByOrderId(String orderId) {
		// 客户信息
		OrderCustomer orderCustomer = orderCustomerService.getOrderCustomerByOrderId(orderId);

		// 库位信息
		List<OrderStorage> orderStorageList = orderStorageService.findOrderStorageByOrderId(orderId);

		if (CollectionUtils.isNotEmpty(orderStorageList)) {
			for (OrderStorage os : orderStorageList) {
				// 库位下资源信息
				os.setOrderSpecList(orderSpecService.findOrderSpecListByStorageId(orderId, os.getId()));
			}
		}
		orderCustomer.setOrderStorageList(orderStorageList);
		return orderCustomer;
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#createStockoutWorkBook(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年4月16日 下午3:28:16
	 */
	@Override
	public Workbook createStockoutWorkBook(String stockoutId) {
		// 创建工作簿
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();

		HSSFCellStyle setBorder = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLightOrange = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderErp = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderSc = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderOrange = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLime = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLemonChiffon = (HSSFCellStyle) workbook.createCellStyle();

		// setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLightOrange.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLightOrange.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLightOrange.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLightOrange.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderErp.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderErp.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderErp.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderErp.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderSc.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderSc.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderSc.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderSc.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderOrange.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderOrange.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderOrange.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderOrange.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLime.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLime.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLime.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLime.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLemonChiffon.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLemonChiffon.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLemonChiffon.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLemonChiffon.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);

		setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

		setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);

		setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);

		setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

		setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

		HSSFFont font = (HSSFFont) workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 16);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		setBorder.setFont(font);
		setBorderErp.setFont(font);
		setBorderSc.setFont(font);
		// 创建第一行表头
		Row firstRow = sheet.createRow(0);
		firstRow.createCell(0).setCellValue("生产工艺单");
		CellRangeAddress region = null;
		region = new CellRangeAddress(0, 0, 0, 9);
		sheet.addMergedRegion(region);

		// 第二行
		Row secondRow = sheet.createRow(1);
		secondRow.createCell(0).setCellValue("ERP侧");
		secondRow.createCell(8).setCellValue("供应链侧");
		region = new CellRangeAddress(1, 1, 0, 7);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(1, 1, 8, 9);
		sheet.addMergedRegion(region);

		// 第三行
		Row thirdRow = sheet.createRow(2);
		thirdRow.createCell(0).setCellValue("原始订单");
		thirdRow.createCell(3).setCellValue("规格分解");
		thirdRow.createCell(5).setCellValue("组成分解");
		thirdRow.createCell(8).setCellValue("SKU匹配");
		region = new CellRangeAddress(2, 2, 0, 2);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 3, 4);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 5, 7);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 8, 9);
		sheet.addMergedRegion(region);

		// 第四行
		Row fourthRow = sheet.createRow(3);
		fourthRow.createCell(0).setCellValue("订单编号");
		fourthRow.createCell(1).setCellValue("实际数量");
		fourthRow.createCell(2).setCellValue("ERP订单商品");
		fourthRow.createCell(3).setCellValue("ERP规格");
		fourthRow.createCell(4).setCellValue("数量");
		fourthRow.createCell(5).setCellValue("原子规格组成");
		fourthRow.createCell(6).setCellValue("四季均价");
		fourthRow.createCell(7).setCellValue("数量");
		fourthRow.createCell(8).setCellValue("供应链商品SKU");
		fourthRow.createCell(9).setCellValue("数量");

		// 样式设置
		firstRow.getCell(0).setCellStyle(setBorder);
		secondRow.getCell(0).setCellStyle(setBorderErp);
		secondRow.getCell(8).setCellStyle(setBorderSc);
		thirdRow.getCell(0).setCellStyle(setBorderErp);
		thirdRow.getCell(3).setCellStyle(setBorderErp);
		thirdRow.getCell(5).setCellStyle(setBorderErp);
		thirdRow.getCell(8).setCellStyle(setBorderSc);
		fourthRow.getCell(0).setCellStyle(setBorderErp);
		fourthRow.getCell(1).setCellStyle(setBorderErp);
		fourthRow.getCell(2).setCellStyle(setBorderErp);
		fourthRow.getCell(3).setCellStyle(setBorderErp);
		fourthRow.getCell(4).setCellStyle(setBorderErp);
		fourthRow.getCell(5).setCellStyle(setBorderErp);
		fourthRow.getCell(6).setCellStyle(setBorderErp);
		fourthRow.getCell(7).setCellStyle(setBorderErp);
		fourthRow.getCell(8).setCellStyle(setBorderSc);
		fourthRow.getCell(9).setCellStyle(setBorderSc);
		sheet.setColumnWidth(0, 5000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 2500);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		sheet.setColumnWidth(7, 2500);
		sheet.setColumnWidth(8, 5000);
		sheet.setColumnWidth(9, 2500);
		List<String> orderIds = orderService.findOrderIdsByEvidenceId(stockoutId);
		Row row;

		// 表二
		Sheet secondSheet = workbook.createSheet();
		Row firstRowOfSecondSheet = secondSheet.createRow(0);
		firstRowOfSecondSheet.createCell(0).setCellValue("供应链拆解单");
		region = new CellRangeAddress(0, 0, 0, 1);
		secondSheet.addMergedRegion(region);
		Row secondRowOfSecondSheet = secondSheet.createRow(1);
		secondRowOfSecondSheet.createCell(0).setCellValue("供应链商品");
		secondRowOfSecondSheet.createCell(1).setCellValue("数量");
		firstRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(1).setCellStyle(setBorder);
		secondSheet.setColumnWidth(0, 8000);
		secondSheet.setColumnWidth(1, 5000);
		List<StockoutSku> skuList = stockoutSkuService.findStockoutSkuByOrderIds(orderIds);
		if (CollectionUtils.isNotEmpty(skuList)) {
			for (int i = 0; i < skuList.size(); i++) {
				row = secondSheet.createRow(i + 2);
				row.createCell(0).setCellValue(skuList.get(i).getSkuName());
				row.createCell(1).setCellValue(skuList.get(i).getAmount());
			}
		}

		// 表三
		Sheet thirdSheet = workbook.createSheet();
		Row firstRowOfThirdSheet = thirdSheet.createRow(0);
		firstRowOfThirdSheet.createCell(0).setCellValue("ERP订单");
		region = new CellRangeAddress(0, 0, 0, 1);
		thirdSheet.addMergedRegion(region);
		Row secondRowOfThirdSheet = thirdSheet.createRow(1);
		secondRowOfThirdSheet.createCell(0).setCellValue("订单商品");
		secondRowOfThirdSheet.createCell(1).setCellValue("数量");
		firstRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(1).setCellStyle(setBorder);
		thirdSheet.setColumnWidth(0, 10000);
		thirdSheet.setColumnWidth(1, 5000);
		List<OrderDetailSummary> orderDetailList = orderDetailSummaryService.findOrderDetailSummary(orderIds);
		if (CollectionUtils.isNotEmpty(orderDetailList)) {
			for (int i = 0; i < orderDetailList.size(); i++) {
				row = thirdSheet.createRow(i + 2);
				row.createCell(0).setCellValue(orderDetailList.get(i).getName());
				row.createCell(1).setCellValue(orderDetailList.get(i).getAmount());
				// XSSFCell cell = (XSSFCell) row.createCell(2);
				// try {
				// ExcelUtil.addimg((XSSFSheet) sheet, (XSSFWorkbook) workbook, orderDetailList.get(i).getImageId(),
				// (short) cell.getColumnIndex(), (short) (cell.getColumnIndex() + 1),
				// thirdSheet.getLastRowNum(), (short) thirdSheet.getLastRowNum() + 1);
				// } catch (Exception e) {
				// e.printStackTrace();
				// }
			}
		}

		// 数据填充
		List<OrderDetailSummary> orderDetailSummaryList = orderDetailSummaryService
				.findOrderDetailSummaryByOrderIds(orderIds);

		int rowNum = 4;
		int thirtListNum = 0;
		int firstListNum = 0;
		int listSize = 0;
		if (CollectionUtils.isNotEmpty(orderDetailSummaryList)) {
			for (int j = 0; j < orderDetailSummaryList.size(); j++) {
				firstListNum = 0;
				List<OrderDetailSummarySpec> orderDetailSummarySpecList = orderDetailSummarySpecService
						.findOrderDetailSummarySpecById(orderDetailSummaryList.get(j).getId());
				if (CollectionUtils.isNotEmpty(orderDetailSummarySpecList)) {
					for (OrderDetailSummarySpec odss : orderDetailSummarySpecList) {
						List<OrderDetailSummarySpecConsist> orderDetailSummarySpecConsistList = orderDetailSummarySpecConsistService
								.findOrderDetailSummarySpecConsist(odss.getSpecId(), odss.getSummaryId(),
										odss.getBasinColor());
						if (CollectionUtils.isNotEmpty(orderDetailSummarySpecConsistList)) {
							for (int i = 0; i < orderDetailSummarySpecConsistList.size(); i++) {
								row = sheet.createRow(i + rowNum);
								row.createCell(0).setCellValue(orderDetailSummarySpecConsistList.get(i).getOrderNo());
								sheet.getRow(i + rowNum).getCell(0).setCellStyle(setBorderLemonChiffon);
								;
								row.createCell(5)
										.setCellValue(orderDetailSummarySpecConsistList.get(i).getConsistSpecName());
								row.createCell(6).setCellValue(orderDetailSummarySpecConsistList.get(i).getSalePrice());
								row.createCell(7).setCellValue(orderDetailSummarySpecConsistList.get(i).getAmount());
								row.createCell(8).setCellValue(orderDetailSummarySpecConsistList.get(i).getSkuName());
								row.createCell(9).setCellValue(orderDetailSummarySpecConsistList.get(i).getAmount());

								// 背景色
								row.getCell(5).setCellStyle(setBorderOrange);
								row.getCell(6).setCellStyle(setBorderOrange);
								row.getCell(7).setCellStyle(setBorderOrange);
								row.getCell(8).setCellStyle(setBorderLime);
								row.getCell(9).setCellStyle(setBorderLime);

							}
							firstListNum = firstListNum + orderDetailSummarySpecConsistList.size();
							rowNum = rowNum + orderDetailSummarySpecConsistList.size();
							thirtListNum = orderDetailSummarySpecConsistList.size();
							listSize = listSize + orderDetailSummarySpecConsistList.size();
						}
						sheet.getRow(rowNum - thirtListNum).createCell(3).setCellValue(odss.getSpecName());
						sheet.getRow(rowNum - thirtListNum).createCell(4).setCellValue(odss.getAmount());

						// 设置养样式
						sheet.getRow(rowNum - thirtListNum).getCell(3).setCellStyle(setBorderLightOrange);
						sheet.getRow(rowNum - thirtListNum).getCell(4).setCellStyle(setBorderLightOrange);

						region = new CellRangeAddress(rowNum - thirtListNum, rowNum - 1, 3, 3);
						sheet.addMergedRegion(region);
						region = new CellRangeAddress(rowNum - thirtListNum, rowNum - 1, 4, 4);
						sheet.addMergedRegion(region);
					}
				}

				sheet.getRow(rowNum - firstListNum).createCell(1)
						.setCellValue(orderDetailSummaryList.get(j).getAmount());
				sheet.getRow(rowNum - firstListNum).createCell(2).setCellValue(orderDetailSummaryList.get(j).getName());
				// 设置样式
				sheet.getRow(rowNum - firstListNum).getCell(1).setCellStyle(setBorderLemonChiffon);
				sheet.getRow(rowNum - firstListNum).getCell(2).setCellStyle(setBorderLemonChiffon);

				region = new CellRangeAddress(rowNum - firstListNum, rowNum - 1, 1, 1);
				sheet.addMergedRegion(region);
				region = new CellRangeAddress(rowNum - firstListNum, rowNum - 1, 2, 2);
				sheet.addMergedRegion(region);

				// if(j==0) {
				// sheet.getRow(4).createCell(0).setCellValue(orderDetailSummaryList.get(0).getOrderNo());
				// }
				// else {
				// if(!orderDetailSummaryList.get(j).getOrderNo().equals(orderDetailSummaryList.get(j-1).getOrderNo()))
				// {
				// sheet.getRow(rowNum-firstListNum).createCell(0).setCellValue(orderDetailSummaryList.get(j).getOrderNo());
				// }
				// }

			}
			int a = 4;
			if (listSize > 1) {
				for (int i = 0; i < listSize; i++) {
					if (i > 0) {
						if (!sheet.getRow(i + 4).getCell(0).getStringCellValue()
								.equals(sheet.getRow(i + 3).getCell(0).getStringCellValue())) {
							region = new CellRangeAddress(a, i + 3, 0, 0);
							sheet.addMergedRegion(region);
							a = i + 4;
						}
					}
				}
				region = new CellRangeAddress(a, listSize + 3, 0, 0);
				sheet.addMergedRegion(region);
			}
		}
		workbook.setSheetName(0, "生产工艺单");
		workbook.setSheetName(1, "供应链拆解单");
		workbook.setSheetName(2, "ERP订单");
		return workbook;
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#createRepairStockoutWorkBook(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年4月22日 下午5:36:18
	 */
	@Override
	public Workbook createRepairStockoutWorkBook(String stockoutId) {
		// 创建工作簿
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();

		HSSFCellStyle setBorder = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLightOrange = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderErp = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderSc = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderOrange = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLime = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLemonChiffon = (HSSFCellStyle) workbook.createCellStyle();

		// setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLightOrange.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLightOrange.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLightOrange.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLightOrange.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderErp.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderErp.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderErp.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderErp.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderSc.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderSc.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderSc.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderSc.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderOrange.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderOrange.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderOrange.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderOrange.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLime.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLime.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLime.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLime.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLemonChiffon.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLemonChiffon.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLemonChiffon.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLemonChiffon.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);

		setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

		setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);

		setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);

		setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

		setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

		HSSFFont font = (HSSFFont) workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 16);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		setBorder.setFont(font);
		setBorderErp.setFont(font);
		setBorderSc.setFont(font);
		// 创建第一行表头
		Row firstRow = sheet.createRow(0);
		firstRow.createCell(0).setCellValue("生产工艺单");
		CellRangeAddress region = null;
		region = new CellRangeAddress(0, 0, 0, 9);
		sheet.addMergedRegion(region);

		// 第二行
		Row secondRow = sheet.createRow(1);
		secondRow.createCell(0).setCellValue("ERP侧");
		secondRow.createCell(8).setCellValue("供应链侧");
		region = new CellRangeAddress(1, 1, 0, 7);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(1, 1, 8, 9);
		sheet.addMergedRegion(region);

		// 第三行
		Row thirdRow = sheet.createRow(2);
		thirdRow.createCell(0).setCellValue("原始复壮单");
		thirdRow.createCell(3).setCellValue("规格分解");
		thirdRow.createCell(5).setCellValue("组成分解");
		thirdRow.createCell(8).setCellValue("SKU匹配");
		region = new CellRangeAddress(2, 2, 0, 2);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 3, 4);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 5, 7);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 8, 9);
		sheet.addMergedRegion(region);

		// 第四行
		Row fourthRow = sheet.createRow(3);
		fourthRow.createCell(0).setCellValue("复壮单号");
		fourthRow.createCell(1).setCellValue("实际数量");
		fourthRow.createCell(2).setCellValue("复壮单商品");
		fourthRow.createCell(3).setCellValue("ERP规格");
		fourthRow.createCell(4).setCellValue("数量");
		fourthRow.createCell(5).setCellValue("原子规格组成");
		fourthRow.createCell(6).setCellValue("四季均价");
		fourthRow.createCell(7).setCellValue("数量");
		fourthRow.createCell(8).setCellValue("供应链商品SKU");
		fourthRow.createCell(9).setCellValue("数量");

		// 样式设置
		firstRow.getCell(0).setCellStyle(setBorder);
		secondRow.getCell(0).setCellStyle(setBorderErp);
		secondRow.getCell(8).setCellStyle(setBorderSc);
		thirdRow.getCell(0).setCellStyle(setBorderErp);
		thirdRow.getCell(3).setCellStyle(setBorderErp);
		thirdRow.getCell(5).setCellStyle(setBorderErp);
		thirdRow.getCell(8).setCellStyle(setBorderSc);
		fourthRow.getCell(0).setCellStyle(setBorderErp);
		fourthRow.getCell(1).setCellStyle(setBorderErp);
		fourthRow.getCell(2).setCellStyle(setBorderErp);
		fourthRow.getCell(3).setCellStyle(setBorderErp);
		fourthRow.getCell(4).setCellStyle(setBorderErp);
		fourthRow.getCell(5).setCellStyle(setBorderErp);
		fourthRow.getCell(6).setCellStyle(setBorderErp);
		fourthRow.getCell(7).setCellStyle(setBorderErp);
		fourthRow.getCell(8).setCellStyle(setBorderSc);
		fourthRow.getCell(9).setCellStyle(setBorderSc);
		sheet.setColumnWidth(0, 5000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 2500);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		sheet.setColumnWidth(7, 2500);
		sheet.setColumnWidth(8, 5000);
		sheet.setColumnWidth(9, 2500);
		List<String> repairIds = orderService.findRepairIdsByEvidenceId(stockoutId);
		Row row;

		// 表二
		Sheet secondSheet = workbook.createSheet();
		Row firstRowOfSecondSheet = secondSheet.createRow(0);
		firstRowOfSecondSheet.createCell(0).setCellValue("供应链拆解单");
		region = new CellRangeAddress(0, 0, 0, 1);
		secondSheet.addMergedRegion(region);
		Row secondRowOfSecondSheet = secondSheet.createRow(1);
		secondRowOfSecondSheet.createCell(0).setCellValue("供应链商品");
		secondRowOfSecondSheet.createCell(1).setCellValue("数量");
		firstRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(1).setCellStyle(setBorder);
		secondSheet.setColumnWidth(0, 8000);
		secondSheet.setColumnWidth(1, 5000);
		List<StockoutSku> skuList = stockoutSkuService.findRepairoutSkuByEvidenceId(stockoutId);
		if (CollectionUtils.isNotEmpty(skuList)) {
			for (int i = 0; i < skuList.size(); i++) {
				row = secondSheet.createRow(i + 2);
				row.createCell(0).setCellValue(skuList.get(i).getSkuName());
				row.createCell(1).setCellValue(skuList.get(i).getAmount());
			}
		}

		// 表三
		Sheet thirdSheet = workbook.createSheet();
		Row firstRowOfThirdSheet = thirdSheet.createRow(0);
		firstRowOfThirdSheet.createCell(0).setCellValue("复壮单");
		region = new CellRangeAddress(0, 0, 0, 1);
		thirdSheet.addMergedRegion(region);
		Row secondRowOfThirdSheet = thirdSheet.createRow(1);
		secondRowOfThirdSheet.createCell(0).setCellValue("复壮单商品");
		secondRowOfThirdSheet.createCell(1).setCellValue("数量");
		firstRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(1).setCellStyle(setBorder);
		thirdSheet.setColumnWidth(0, 10000);
		thirdSheet.setColumnWidth(1, 5000);
		List<OrderDetailSummary> repairDetailList = orderDetailSummaryService.findRepairDetailSummary(repairIds);
		if (CollectionUtils.isNotEmpty(repairDetailList)) {
			for (int i = 0; i < repairDetailList.size(); i++) {
				row = thirdSheet.createRow(i + 2);
				row.createCell(0).setCellValue(repairDetailList.get(i).getName());
				row.createCell(1).setCellValue(repairDetailList.get(i).getAmount());
			}
		}

		// 数据填充
		List<OrderDetailSummary> repairDetailSummaryList = orderDetailSummaryService
				.findRepairDetailSummaryByRepairIds(repairIds);

		int rowNum = 4;
		int thirtListNum = 0;
		int firstListNum = 0;
		int listSize = 0;
		if (CollectionUtils.isNotEmpty(repairDetailSummaryList)) {
			for (int j = 0; j < repairDetailSummaryList.size(); j++) {
				firstListNum = 0;
				List<OrderDetailSummarySpec> repairDetailSummarySpecList = orderDetailSummarySpecService
						.findRepairDetailSummarySpecById(repairDetailSummaryList.get(j).getId());
				if (CollectionUtils.isNotEmpty(repairDetailSummarySpecList)) {
					for (OrderDetailSummarySpec odss : repairDetailSummarySpecList) {
						List<OrderDetailSummarySpecConsist> repairDetailSummarySpecConsistList = orderDetailSummarySpecConsistService
								.findRepairDetailSummarySpecConsist(odss.getSpecId(), odss.getSummaryId(),
										odss.getBasinColor());
						if (CollectionUtils.isNotEmpty(repairDetailSummarySpecConsistList)) {
							for (int i = 0; i < repairDetailSummarySpecConsistList.size(); i++) {
								row = sheet.createRow(i + rowNum);
								row.createCell(0).setCellValue(repairDetailSummarySpecConsistList.get(i).getOrderNo());
								sheet.getRow(i + rowNum).getCell(0).setCellStyle(setBorderLemonChiffon);
								;
								row.createCell(5)
										.setCellValue(repairDetailSummarySpecConsistList.get(i).getConsistSpecName());
								row.createCell(6)
										.setCellValue(repairDetailSummarySpecConsistList.get(i).getSalePrice());
								row.createCell(7).setCellValue(repairDetailSummarySpecConsistList.get(i).getAmount());
								row.createCell(8).setCellValue(repairDetailSummarySpecConsistList.get(i).getSkuName());
								row.createCell(9).setCellValue(repairDetailSummarySpecConsistList.get(i).getAmount());

								// 背景色
								row.getCell(5).setCellStyle(setBorderOrange);
								row.getCell(6).setCellStyle(setBorderOrange);
								row.getCell(7).setCellStyle(setBorderOrange);
								row.getCell(8).setCellStyle(setBorderLime);
								row.getCell(9).setCellStyle(setBorderLime);

							}
							firstListNum = firstListNum + repairDetailSummarySpecConsistList.size();
							rowNum = rowNum + repairDetailSummarySpecConsistList.size();
							thirtListNum = repairDetailSummarySpecConsistList.size();
							listSize = listSize + repairDetailSummarySpecConsistList.size();
						}
						sheet.getRow(rowNum - thirtListNum).createCell(3).setCellValue(odss.getSpecName());
						sheet.getRow(rowNum - thirtListNum).createCell(4).setCellValue(odss.getAmount());

						// 设置养样式
						sheet.getRow(rowNum - thirtListNum).getCell(3).setCellStyle(setBorderLightOrange);
						sheet.getRow(rowNum - thirtListNum).getCell(4).setCellStyle(setBorderLightOrange);
						;

						region = new CellRangeAddress(rowNum - thirtListNum, rowNum - 1, 3, 3);
						sheet.addMergedRegion(region);
						region = new CellRangeAddress(rowNum - thirtListNum, rowNum - 1, 4, 4);
						sheet.addMergedRegion(region);
					}
				}

				sheet.getRow(rowNum - firstListNum).createCell(1)
						.setCellValue(repairDetailSummaryList.get(j).getAmount());
				sheet.getRow(rowNum - firstListNum).createCell(2)
						.setCellValue(repairDetailSummaryList.get(j).getName());
				// 设置样式
				sheet.getRow(rowNum - firstListNum).getCell(1).setCellStyle(setBorderLemonChiffon);
				sheet.getRow(rowNum - firstListNum).getCell(2).setCellStyle(setBorderLemonChiffon);

				region = new CellRangeAddress(rowNum - firstListNum, rowNum - 1, 1, 1);
				sheet.addMergedRegion(region);
				region = new CellRangeAddress(rowNum - firstListNum, rowNum - 1, 2, 2);
				sheet.addMergedRegion(region);

				// if(j==0) {
				// sheet.getRow(4).createCell(0).setCellValue(orderDetailSummaryList.get(0).getOrderNo());
				// }
				// else {
				// if(!orderDetailSummaryList.get(j).getOrderNo().equals(orderDetailSummaryList.get(j-1).getOrderNo()))
				// {
				// sheet.getRow(rowNum-firstListNum).createCell(0).setCellValue(orderDetailSummaryList.get(j).getOrderNo());
				// }
				// }

			}
			int a = 4;
			if (listSize > 1) {
				for (int i = 0; i < listSize; i++) {
					if (i > 0) {
						if (!sheet.getRow(i + 4).getCell(0).getStringCellValue()
								.equals(sheet.getRow(i + 3).getCell(0).getStringCellValue())) {
							region = new CellRangeAddress(a, i + 3, 0, 0);
							sheet.addMergedRegion(region);
							a = i + 4;
						}
					}
				}
				region = new CellRangeAddress(a, listSize + 3, 0, 0);
				sheet.addMergedRegion(region);
			}
		}
		workbook.setSheetName(0, "生产工艺单");
		workbook.setSheetName(1, "供应链拆解单");
		workbook.setSheetName(2, "复壮单");
		return workbook;
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#createStockoutWorkBookAll(java.util.List)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年9月9日 上午11:02:03
	 */
	@Override
	public Workbook createStockoutWorkBookAll(List<String> stockoutIds) {
		// 创建工作簿
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();

		HSSFCellStyle setBorder = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLightOrange = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderErp = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderSc = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderOrange = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLime = (HSSFCellStyle) workbook.createCellStyle();
		HSSFCellStyle setBorderLemonChiffon = (HSSFCellStyle) workbook.createCellStyle();

		// setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLightOrange.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLightOrange.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLightOrange.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLightOrange.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderErp.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderErp.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderErp.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderErp.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderSc.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderSc.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderSc.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderSc.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderOrange.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderOrange.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderOrange.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderOrange.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLime.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLime.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLime.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLime.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		// setBorderLemonChiffon.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		// setBorderLemonChiffon.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		// setBorderLemonChiffon.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// setBorderLemonChiffon.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);

		setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

		setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);

		setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);

		setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

		setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

		HSSFFont font = (HSSFFont) workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 16);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		setBorder.setFont(font);
		setBorderErp.setFont(font);
		setBorderSc.setFont(font);
		// 创建第一行表头
		Row firstRow = sheet.createRow(0);
		firstRow.createCell(0).setCellValue("生产工艺单");
		CellRangeAddress region = null;
		region = new CellRangeAddress(0, 0, 0, 9);
		sheet.addMergedRegion(region);

		// 第二行
		Row secondRow = sheet.createRow(1);
		secondRow.createCell(0).setCellValue("ERP侧");
		secondRow.createCell(8).setCellValue("供应链侧");
		region = new CellRangeAddress(1, 1, 0, 7);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(1, 1, 8, 9);
		sheet.addMergedRegion(region);

		// 第三行
		Row thirdRow = sheet.createRow(2);
		thirdRow.createCell(0).setCellValue("原始订单");
		thirdRow.createCell(3).setCellValue("规格分解");
		thirdRow.createCell(5).setCellValue("组成分解");
		thirdRow.createCell(8).setCellValue("SKU匹配");
		region = new CellRangeAddress(2, 2, 0, 2);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 3, 4);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 5, 7);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 8, 9);
		sheet.addMergedRegion(region);

		// 第四行
		Row fourthRow = sheet.createRow(3);
		fourthRow.createCell(0).setCellValue("订单编号");
		fourthRow.createCell(1).setCellValue("实际数量");
		fourthRow.createCell(2).setCellValue("ERP订单商品");
		fourthRow.createCell(3).setCellValue("ERP规格");
		fourthRow.createCell(4).setCellValue("数量");
		fourthRow.createCell(5).setCellValue("原子规格组成");
		fourthRow.createCell(6).setCellValue("四季均价");
		fourthRow.createCell(7).setCellValue("数量");
		fourthRow.createCell(8).setCellValue("供应链商品SKU");
		fourthRow.createCell(9).setCellValue("数量");

		// 样式设置
		firstRow.getCell(0).setCellStyle(setBorder);
		secondRow.getCell(0).setCellStyle(setBorderErp);
		secondRow.getCell(8).setCellStyle(setBorderSc);
		thirdRow.getCell(0).setCellStyle(setBorderErp);
		thirdRow.getCell(3).setCellStyle(setBorderErp);
		thirdRow.getCell(5).setCellStyle(setBorderErp);
		thirdRow.getCell(8).setCellStyle(setBorderSc);
		fourthRow.getCell(0).setCellStyle(setBorderErp);
		fourthRow.getCell(1).setCellStyle(setBorderErp);
		fourthRow.getCell(2).setCellStyle(setBorderErp);
		fourthRow.getCell(3).setCellStyle(setBorderErp);
		fourthRow.getCell(4).setCellStyle(setBorderErp);
		fourthRow.getCell(5).setCellStyle(setBorderErp);
		fourthRow.getCell(6).setCellStyle(setBorderErp);
		fourthRow.getCell(7).setCellStyle(setBorderErp);
		fourthRow.getCell(8).setCellStyle(setBorderSc);
		fourthRow.getCell(9).setCellStyle(setBorderSc);
		sheet.setColumnWidth(0, 5000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 2500);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		sheet.setColumnWidth(7, 2500);
		sheet.setColumnWidth(8, 5000);
		sheet.setColumnWidth(9, 2500);
		List<String> orderIds = orderService.findOrderIdsByEvidenceIds(stockoutIds);
		Row row;

		// 表二
		Sheet secondSheet = workbook.createSheet();
		Row firstRowOfSecondSheet = secondSheet.createRow(0);
		firstRowOfSecondSheet.createCell(0).setCellValue("供应链拆解单");
		region = new CellRangeAddress(0, 0, 0, 1);
		secondSheet.addMergedRegion(region);
		Row secondRowOfSecondSheet = secondSheet.createRow(1);
		secondRowOfSecondSheet.createCell(0).setCellValue("供应链商品");
		secondRowOfSecondSheet.createCell(1).setCellValue("数量");
		firstRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(1).setCellStyle(setBorder);
		secondSheet.setColumnWidth(0, 8000);
		secondSheet.setColumnWidth(1, 5000);
		List<StockoutSku> skuList = stockoutSkuService.findStockoutSkuByOrderIds(orderIds);
		if (CollectionUtils.isNotEmpty(skuList)) {
			for (int i = 0; i < skuList.size(); i++) {
				row = secondSheet.createRow(i + 2);
				row.createCell(0).setCellValue(skuList.get(i).getSkuName());
				row.createCell(1).setCellValue(skuList.get(i).getAmount());
			}
		}

		// 表三
		Sheet thirdSheet = workbook.createSheet();
		Row firstRowOfThirdSheet = thirdSheet.createRow(0);
		firstRowOfThirdSheet.createCell(0).setCellValue("ERP订单");
		region = new CellRangeAddress(0, 0, 0, 1);
		thirdSheet.addMergedRegion(region);
		Row secondRowOfThirdSheet = thirdSheet.createRow(1);
		secondRowOfThirdSheet.createCell(0).setCellValue("订单商品");
		secondRowOfThirdSheet.createCell(1).setCellValue("数量");
		firstRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(1).setCellStyle(setBorder);
		thirdSheet.setColumnWidth(0, 10000);
		thirdSheet.setColumnWidth(1, 5000);
		List<OrderDetailSummary> orderDetailList = orderDetailSummaryService.findOrderDetailSummary(orderIds);
		if (CollectionUtils.isNotEmpty(orderDetailList)) {
			for (int i = 0; i < orderDetailList.size(); i++) {
				row = thirdSheet.createRow(i + 2);
				row.createCell(0).setCellValue(orderDetailList.get(i).getName());
				row.createCell(1).setCellValue(orderDetailList.get(i).getAmount());
			}
		}

		// 数据填充
		List<OrderDetailSummary> orderDetailSummaryList = orderDetailSummaryService
				.findOrderDetailSummaryByOrderIds(orderIds);

		int rowNum = 4;
		int thirtListNum = 0;
		int firstListNum = 0;
		int listSize = 0;
		if (CollectionUtils.isNotEmpty(orderDetailSummaryList)) {
			for (int j = 0; j < orderDetailSummaryList.size(); j++) {
				firstListNum = 0;
				List<OrderDetailSummarySpec> orderDetailSummarySpecList = orderDetailSummarySpecService
						.findOrderDetailSummarySpecById(orderDetailSummaryList.get(j).getId());
				if (CollectionUtils.isNotEmpty(orderDetailSummarySpecList)) {
					for (OrderDetailSummarySpec odss : orderDetailSummarySpecList) {
						List<OrderDetailSummarySpecConsist> orderDetailSummarySpecConsistList = orderDetailSummarySpecConsistService
								.findOrderDetailSummarySpecConsist(odss.getSpecId(), odss.getSummaryId(),
										odss.getBasinColor());
						if (CollectionUtils.isNotEmpty(orderDetailSummarySpecConsistList)) {
							for (int i = 0; i < orderDetailSummarySpecConsistList.size(); i++) {
								row = sheet.createRow(i + rowNum);
								row.createCell(0).setCellValue(orderDetailSummarySpecConsistList.get(i).getOrderNo());
								sheet.getRow(i + rowNum).getCell(0).setCellStyle(setBorderLemonChiffon);
								;
								row.createCell(5)
										.setCellValue(orderDetailSummarySpecConsistList.get(i).getConsistSpecName());
								row.createCell(6).setCellValue(orderDetailSummarySpecConsistList.get(i).getSalePrice());
								row.createCell(7).setCellValue(orderDetailSummarySpecConsistList.get(i).getAmount());
								row.createCell(8).setCellValue(orderDetailSummarySpecConsistList.get(i).getSkuName());
								row.createCell(9).setCellValue(orderDetailSummarySpecConsistList.get(i).getAmount());

								// 背景色
								row.getCell(5).setCellStyle(setBorderOrange);
								row.getCell(6).setCellStyle(setBorderOrange);
								row.getCell(7).setCellStyle(setBorderOrange);
								row.getCell(8).setCellStyle(setBorderLime);
								row.getCell(9).setCellStyle(setBorderLime);

							}
							firstListNum = firstListNum + orderDetailSummarySpecConsistList.size();
							rowNum = rowNum + orderDetailSummarySpecConsistList.size();
							thirtListNum = orderDetailSummarySpecConsistList.size();
							listSize = listSize + orderDetailSummarySpecConsistList.size();
						}
						sheet.getRow(rowNum - thirtListNum).createCell(3).setCellValue(odss.getSpecName());
						sheet.getRow(rowNum - thirtListNum).createCell(4).setCellValue(odss.getAmount());

						// 设置养样式
						sheet.getRow(rowNum - thirtListNum).getCell(3).setCellStyle(setBorderLightOrange);
						sheet.getRow(rowNum - thirtListNum).getCell(4).setCellStyle(setBorderLightOrange);
						;

						region = new CellRangeAddress(rowNum - thirtListNum, rowNum - 1, 3, 3);
						sheet.addMergedRegion(region);
						region = new CellRangeAddress(rowNum - thirtListNum, rowNum - 1, 4, 4);
						sheet.addMergedRegion(region);
					}
				}

				sheet.getRow(rowNum - firstListNum).createCell(1)
						.setCellValue(orderDetailSummaryList.get(j).getAmount());
				sheet.getRow(rowNum - firstListNum).createCell(2).setCellValue(orderDetailSummaryList.get(j).getName());
				// 设置样式
				sheet.getRow(rowNum - firstListNum).getCell(1).setCellStyle(setBorderLemonChiffon);
				sheet.getRow(rowNum - firstListNum).getCell(2).setCellStyle(setBorderLemonChiffon);

				region = new CellRangeAddress(rowNum - firstListNum, rowNum - 1, 1, 1);
				sheet.addMergedRegion(region);
				region = new CellRangeAddress(rowNum - firstListNum, rowNum - 1, 2, 2);
				sheet.addMergedRegion(region);

				// if(j==0) {
				// sheet.getRow(4).createCell(0).setCellValue(orderDetailSummaryList.get(0).getOrderNo());
				// }
				// else {
				// if(!orderDetailSummaryList.get(j).getOrderNo().equals(orderDetailSummaryList.get(j-1).getOrderNo()))
				// {
				// sheet.getRow(rowNum-firstListNum).createCell(0).setCellValue(orderDetailSummaryList.get(j).getOrderNo());
				// }
				// }

			}
			int a = 4;
			if (listSize > 1) {
				for (int i = 0; i < listSize; i++) {
					if (i > 0) {
						if (!sheet.getRow(i + 4).getCell(0).getStringCellValue()
								.equals(sheet.getRow(i + 3).getCell(0).getStringCellValue())) {
							region = new CellRangeAddress(a, i + 3, 0, 0);
							sheet.addMergedRegion(region);
							a = i + 4;
						}
					}
				}
				region = new CellRangeAddress(a, listSize + 3, 0, 0);
				sheet.addMergedRegion(region);
			}
		}
		workbook.setSheetName(0, "生产工艺单");
		workbook.setSheetName(1, "供应链拆解单");
		workbook.setSheetName(2, "ERP订单");
		return workbook;
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findOrderDetail(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年9月23日 下午3:24:12
	 */
	@Override
	public OrderDetailVo findOrderDetail(String orderId) {
		OrderDetailVo detailVo = orderCustomerService.getCustomerByOrderId(orderId);
		detailVo.setOrderSpecList(orderSpecService.findOrderSpecList(orderId));
		return detailVo;
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findRepairByCustomerId(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年9月23日 下午4:08:35
	 */
	@Override
	public List<OrderSpec> findRepairByCustomerId(String customerId) {
		return orderSpecService.findRepairByCustomerId(customerId);
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findOrderDetailByCustomerId(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年9月23日 下午4:54:07
	 */
	@Override
	public List<OrderSpec> findOrderDetailByCustomerId(String customerId) {
		return orderSpecService.findOrderDetailByCustomerId(customerId);
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findSummaryByCustomerId(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年9月23日 下午5:24:34
	 */
	@Override
	public List<OrderSpec> findSummaryByCustomerId(String customerId) {
		return orderSpecService.findSummaryByCustomerId(customerId);
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findCustomerDetail(java.lang.String, java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年9月27日 上午9:44:16
	 */
	@Override
	public List<OrderSpec> findCustomerDetail(String customerId, String serviceType) {
		return orderSpecService.findCustomerDetail(customerId, serviceType);
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#createWorkbookByEvidenceIds(java.util.List)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年9月29日 上午10:55:38
	 */
	@Override
	public Workbook createWorkbookByEvidenceIds(List<String> evidenceIds, String isSupplement) {
		Workbook workbook = new XSSFWorkbook();

		List<ErpSkuDetail> all = orderSpecService.findAll(evidenceIds, isSupplement);

		/**
		 * sheet1 按项目合并
		 */
		createSecondSheet(workbook, evidenceIds, all);

		/**
		 * sheet2 按品种合并
		 */
		Sheet sheet = workbook.createSheet("按品种");

		XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLightOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderErp = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderSc = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLime = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLemonChiffon = (XSSFCellStyle) workbook.createCellStyle();

		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);

		setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

		setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);

		// setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);

		setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

		setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 16);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		setBorder.setFont(font);
		setBorderErp.setFont(font);
		setBorderSc.setFont(font);

		// 创建第一行表头
		Row firstRow = sheet.createRow(0);
		firstRow.createCell(0).setCellValue("南京植百汇智能科技服务有限公司");

		CellRangeAddress region = null;
		region = new CellRangeAddress(0, 0, 0, 6);
		sheet.addMergedRegion(region);

		Row secondRow = sheet.createRow(1);
		secondRow.createCell(0).setCellValue("生产工艺单");
		region = new CellRangeAddress(1, 1, 0, 6);
		sheet.addMergedRegion(region);

		// 第二行
		// Row secondRow = sheet.createRow(1);
		// secondRow.createCell(0).setCellValue("ERP侧");
		// secondRow.createCell(5).setCellValue("供应链侧");
		// region = new CellRangeAddress(1, 1, 0, 4);
		// sheet.addMergedRegion(region);
		// region = new CellRangeAddress(1, 1, 5, 6);
		// sheet.addMergedRegion(region);

		// 第三行
		Row thirdRow = sheet.createRow(2);
		thirdRow.createCell(0).setCellValue("原始订单");
		thirdRow.createCell(3).setCellValue("规格分解");
		thirdRow.createCell(5).setCellValue("SKU原料");
		region = new CellRangeAddress(2, 2, 0, 2);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 3, 4);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 5, 6);
		sheet.addMergedRegion(region);

		// 第四行
		Row fourthRow = sheet.createRow(3);
		fourthRow.createCell(0).setCellValue("图片");
		fourthRow.createCell(1).setCellValue("ERP订单商品");
		fourthRow.createCell(2).setCellValue("实际数量");
		fourthRow.createCell(3).setCellValue("ERP规格");
		fourthRow.createCell(4).setCellValue("数量");
		fourthRow.createCell(5).setCellValue("原料");
		fourthRow.createCell(6).setCellValue("数量");

		// 样式设置
		firstRow.getCell(0).setCellStyle(setBorder);
		secondRow.getCell(0).setCellStyle(setBorder);
		// secondRow.getCell(0).setCellStyle(setBorderErp);
		// secondRow.getCell(5).setCellStyle(setBorderSc);
		thirdRow.getCell(0).setCellStyle(setBorderErp);
		thirdRow.getCell(3).setCellStyle(setBorderErp);
		thirdRow.getCell(5).setCellStyle(setBorderSc);
		fourthRow.getCell(0).setCellStyle(setBorderErp);
		fourthRow.getCell(1).setCellStyle(setBorderErp);
		fourthRow.getCell(2).setCellStyle(setBorderErp);
		fourthRow.getCell(3).setCellStyle(setBorderErp);
		fourthRow.getCell(4).setCellStyle(setBorderErp);
		fourthRow.getCell(5).setCellStyle(setBorderSc);
		fourthRow.getCell(6).setCellStyle(setBorderSc);
		sheet.setColumnWidth(0, 5000);
		sheet.setColumnWidth(1, 20000);
		sheet.setColumnWidth(2, 1000);
		sheet.setColumnWidth(3, 15000);
		sheet.setColumnWidth(4, 1000);
		sheet.setColumnWidth(5, 20000);
		sheet.setColumnWidth(6, 1000);

		Row row;
		int a = 4;
		int b = 4;

		// productSpec-summaryId-orderAmount
		Map<String, Map<String, Integer>> productSpecSummaryIdAmount = new HashMap<String, Map<String, Integer>>();

		// productSpec-summaryId-erpsku
		Map<String, Map.Entry<String, Set<ErpSpecSku>>> proSpecSummaryIdSkuMap = new HashMap<String, Map.Entry<String, Set<ErpSpecSku>>>();

		/**
		 * 以上为原始、中间数据结构
		 * 
		 * 以下为目标数据结构
		 */

		// productSpec集合
		Set<String> productSpecSet = new HashSet<>();

		// productSpec-productSpecName
		Map<String, String> productSpecNameMap = new HashMap<String, String>();

		// productSpec-orderAmount
		Map<String, Integer> productSpecAmountMap = new HashMap<String, Integer>();

		// productSpec-erpsku
		Map<String, Set<ErpSpecSku>> proSpecSkuMap = new HashMap<String, Set<ErpSpecSku>>();

		if (CollectionUtils.isNotEmpty(all)) {
			for (ErpSkuDetail erpSkuDetail : all) {

				addProductSpecSummaryIdAmount(productSpecSummaryIdAmount, erpSkuDetail);
				addProductSpecSkuAmount(proSpecSummaryIdSkuMap, erpSkuDetail);
				productSpecSet.add(erpSkuDetail.getProductSpecGroupKey());
				productSpecNameMap.putIfAbsent(erpSkuDetail.getProductSpecGroupKey(), erpSkuDetail.getFullName());
			}
		}

		for (String productSpeckey : productSpecSet) {
			Integer total = 0;
			for (Integer summaryIdAmount : productSpecSummaryIdAmount.get(productSpeckey).values()) {
				total += summaryIdAmount;
			}
			productSpecAmountMap.put(productSpeckey, total);

			proSpecSkuMap.put(productSpeckey, proSpecSummaryIdSkuMap.get(productSpeckey).getValue());

		}

		// Iterator<Entry<String, Set<ErpSpecSku>>> it = proSpecSkuMap.entrySet().iterator();
		// while (it.hasNext()) {
		// Entry<String, Set<ErpSpecSku>> entry = it.next();
		// Set<ErpSpecSku> erpSpecSkuSet = entry.getValue();
		// for (ErpSpecSku s : erpSpecSkuSet) {
		// System.out.println(s.getSpecId());
		// }
		// }

		if (!productSpecAmountMap.isEmpty()) {
			for (String key : productSpecAmountMap.keySet()) {
				Set<ErpSpecSku> erpSpecSkuSet = proSpecSkuMap.get(key);
				if (CollectionUtils.isNotEmpty(erpSpecSkuSet)) {
					ErpSpecSku erpSpecSku = new ErpSpecSku();
					for (ErpSpecSku es : erpSpecSkuSet) {
						erpSpecSku = es;
						row = sheet.createRow(a);
						sheet.getRow(a).setHeight((short) 1200);
						row.createCell(3).setCellValue(es.getSpecName());
						row.createCell(4).setCellValue(es.getAmount());
						row.createCell(5).setCellValue(es.getSkuName());
						row.createCell(6).setCellValue(es.getSkuAmount() * es.getAmount());

						a = a + 1;
					}
					sheet.getRow(b).createCell(1).setCellValue(productSpecNameMap.get(key));
					sheet.getRow(b).createCell(2).setCellValue(productSpecAmountMap.get(key));

					XSSFCell cell = (XSSFCell) sheet.getRow(b).createCell(0);
					// try {
					// ExcelUtil.addimg((XSSFSheet) sheet, (XSSFWorkbook) workbook, erpSpecSku.getImageUrl(),
					// (short) cell.getColumnIndex(), (short) (cell.getColumnIndex() + 1),
					// sheet.getLastRowNum(), (short) sheet.getLastRowNum() + 1);
					// } catch (Exception e) {
					// e.printStackTrace();
					// }
					region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 1, 1);
					sheet.addMergedRegion(region);
					region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 2, 2);
					sheet.addMergedRegion(region);
					region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 0, 0);
					sheet.addMergedRegion(region);
					b = b + erpSpecSkuSet.size();
				}

			}

		}

		/**
		 * sheet3 领料单
		 */
		Sheet secondSheet = workbook.createSheet("领料单");
		Row firstRowOfSecondSheet = secondSheet.createRow(0);
		firstRowOfSecondSheet.createCell(0).setCellValue("领料单");
		region = new CellRangeAddress(0, 0, 0, 1);
		secondSheet.addMergedRegion(region);
		Row secondRowOfSecondSheet = secondSheet.createRow(1);
		secondRowOfSecondSheet.createCell(0).setCellValue("原料");
		secondRowOfSecondSheet.createCell(1).setCellValue("数量");
		firstRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(1).setCellStyle(setBorder);
		secondSheet.setColumnWidth(0, 20000);
		secondSheet.setColumnWidth(1, 1000);
		List<StockoutSku> skuList = stockoutSkuService.findSkuByEvidenceIds(evidenceIds);
		if (CollectionUtils.isNotEmpty(skuList)) {
			for (int i = 0; i < skuList.size(); i++) {
				row = secondSheet.createRow(i + 2);
				row.createCell(0).setCellValue(skuList.get(i).getSkuName());
				row.createCell(1).setCellValue(skuList.get(i).getAmount());
			}
		}

		/**
		 * sheet4 ERP商品清单
		 */
		Sheet thirdSheet = workbook.createSheet("ERP商品清单");
		Row firstRowOfThirdSheet = thirdSheet.createRow(0);
		firstRowOfThirdSheet.createCell(0).setCellValue("ERP商品清单");
		region = new CellRangeAddress(0, 0, 0, 2);
		thirdSheet.addMergedRegion(region);
		Row secondRowOfThirdSheet = thirdSheet.createRow(1);
		secondRowOfThirdSheet.createCell(0).setCellValue("订单商品");
		secondRowOfThirdSheet.createCell(1).setCellValue("数量");
		secondRowOfThirdSheet.createCell(2).setCellValue("图片");
		firstRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(1).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(2).setCellStyle(setBorder);
		thirdSheet.setColumnWidth(0, 20000);
		thirdSheet.setColumnWidth(1, 1000);
		thirdSheet.setColumnWidth(2, 3000);
		List<OrderDetailSummary> orderDetailList = orderDetailSummaryService.findErpSummary(evidenceIds);
		if (CollectionUtils.isNotEmpty(orderDetailList)) {
			for (int i = 0; i < orderDetailList.size(); i++) {
				row = thirdSheet.createRow(i + 2);
				thirdSheet.getRow(i + 2).setHeight((short) 1000);
				row.createCell(0).setCellValue(orderDetailList.get(i).getName());
				row.createCell(1).setCellValue(orderDetailList.get(i).getAmount());
				XSSFCell cell = (XSSFCell) row.createCell(2);
				// try {
				// ExcelUtil.addimg((XSSFSheet) thirdSheet, (XSSFWorkbook) workbook,
				// orderDetailList.get(i).getImageId(), (short) cell.getColumnIndex(),
				// (short) (cell.getColumnIndex() + 1), thirdSheet.getLastRowNum(),
				// (short) thirdSheet.getLastRowNum() + 1);
				// } catch (Exception e) {
				// e.printStackTrace();
				// }
			}
		}

		return workbook;
	}

	@SuppressWarnings("deprecation")
	public static void main(String[] args) throws IOException {
		// //创建工作簿
		// Workbook workbook=new HSSFWorkbook();
		// Sheet sheet=workbook.createSheet();
		// HSSFCellStyle setBorder = (HSSFCellStyle) workbook.createCellStyle();
		// HSSFCellStyle setBorderLightOrange = (HSSFCellStyle) workbook.createCellStyle();
		// HSSFCellStyle setBorderErp = (HSSFCellStyle) workbook.createCellStyle();
		// HSSFCellStyle setBorderSc = (HSSFCellStyle) workbook.createCellStyle();
		// HSSFCellStyle setBorderOrange = (HSSFCellStyle) workbook.createCellStyle();
		// HSSFCellStyle setBorderLime = (HSSFCellStyle) workbook.createCellStyle();
		// HSSFCellStyle setBorderLemonChiffon = (HSSFCellStyle) workbook.createCellStyle();
		//
		//
		//// setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		//// setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		//// setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		//// setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		////
		//// setBorderLightOrange.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		//// setBorderLightOrange.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		//// setBorderLightOrange.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		//// setBorderLightOrange.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		////
		//// setBorderErp.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		//// setBorderErp.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		//// setBorderErp.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		//// setBorderErp.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		////
		//// setBorderSc.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		//// setBorderSc.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		//// setBorderSc.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		//// setBorderSc.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		////
		//// setBorderOrange.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		//// setBorderOrange.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		//// setBorderOrange.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		//// setBorderOrange.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		////
		//// setBorderLime.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		//// setBorderLime.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		//// setBorderLime.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		//// setBorderLime.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		////
		//// setBorderLemonChiffon.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		//// setBorderLemonChiffon.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		//// setBorderLemonChiffon.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		//// setBorderLemonChiffon.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		//
		//
		//
		//
		//
		//
		//
		//
		// setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		//
		// setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);
		//
		// setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		//
		// setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);
		//
		// setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);
		//
		// setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		// setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
		//
		// setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		// setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
		//
		//
		// HSSFFont font = (HSSFFont) workbook.createFont();
		// font.setFontName("黑体");
		// font.setFontHeightInPoints((short) 16);//设置字体大小
		// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
		// setBorder.setFont(font);
		// setBorderErp.setFont(font);
		// setBorderSc.setFont(font);
		// //创建第一行表头
		// Row firstRow=sheet.createRow(0);
		// firstRow.createCell(0).setCellValue("生产工艺单");
		// CellRangeAddress region = null;
		// region=new CellRangeAddress(0, 0, 0, 9);
		// sheet.addMergedRegion(region);
		//
		// //第二行
		// Row secondRow=sheet.createRow(1);
		// secondRow.createCell(0).setCellValue("ERP侧");
		// secondRow.createCell(8).setCellValue("供应链侧");
		// region=new CellRangeAddress(1, 1, 0, 7);
		// sheet.addMergedRegion(region);
		// region=new CellRangeAddress(1, 1, 8, 9);
		// sheet.addMergedRegion(region);
		//
		// //第三行
		// Row thirdRow=sheet.createRow(2);
		// thirdRow.createCell(0).setCellValue("原始订单");
		// thirdRow.createCell(3).setCellValue("规格分解");
		// thirdRow.createCell(5).setCellValue("组成分解");
		// thirdRow.createCell(8).setCellValue("SKU匹配");
		// region=new CellRangeAddress(2, 2, 0, 2);
		// sheet.addMergedRegion(region);
		// region=new CellRangeAddress(2, 2, 3, 4);
		// sheet.addMergedRegion(region);
		// region=new CellRangeAddress(2, 2, 5, 7);
		// sheet.addMergedRegion(region);
		// region=new CellRangeAddress(2, 2, 8, 9);
		// sheet.addMergedRegion(region);
		//
		// //第四行
		// Row fourthRow=sheet.createRow(3);
		// fourthRow.createCell(0).setCellValue("订单编号");
		// fourthRow.createCell(1).setCellValue("实际数量");
		// fourthRow.createCell(2).setCellValue("ERP订单商品");
		// fourthRow.createCell(3).setCellValue("ERP规格");
		// fourthRow.createCell(4).setCellValue("数量");
		// fourthRow.createCell(5).setCellValue("原子规格组成");
		// fourthRow.createCell(6).setCellValue("四季均价");
		// fourthRow.createCell(7).setCellValue("数量");
		// fourthRow.createCell(8).setCellValue("供应链商品SKU");
		// fourthRow.createCell(9).setCellValue("数量");
		//
		// //样式设置
		// firstRow.getCell(0).setCellStyle(setBorder);
		// secondRow.getCell(0).setCellStyle(setBorderErp);
		// secondRow.getCell(8).setCellStyle(setBorderSc);
		// thirdRow.getCell(0).setCellStyle(setBorderErp);
		// thirdRow.getCell(3).setCellStyle(setBorderErp);
		// thirdRow.getCell(5).setCellStyle(setBorderErp);
		// thirdRow.getCell(8).setCellStyle(setBorderSc);
		// fourthRow.getCell(0).setCellStyle(setBorderErp);
		// fourthRow.getCell(1).setCellStyle(setBorderErp);
		// fourthRow.getCell(2).setCellStyle(setBorderErp);
		// fourthRow.getCell(3).setCellStyle(setBorderErp);
		// fourthRow.getCell(4).setCellStyle(setBorderErp);
		// fourthRow.getCell(5).setCellStyle(setBorderErp);
		// fourthRow.getCell(6).setCellStyle(setBorderErp);
		// fourthRow.getCell(7).setCellStyle(setBorderErp);
		// fourthRow.getCell(8).setCellStyle(setBorderSc);
		// fourthRow.getCell(9).setCellStyle(setBorderSc);
		// sheet.setColumnWidth(0,5000);
		// sheet.setColumnWidth(1,5000);
		// sheet.setColumnWidth(2,5000);
		// sheet.setColumnWidth(3,5000);
		// sheet.setColumnWidth(4,2500);
		// sheet.setColumnWidth(5,5000);
		// sheet.setColumnWidth(6,5000);
		// sheet.setColumnWidth(7,2500);
		// sheet.setColumnWidth(8,5000);
		// sheet.setColumnWidth(9,2500);
		//
		// //数据填充
		// List<String> orderIds=new ArrayList<String>();
		// orderIds.add("737c68080f3f47d98cb42f28732ce5d3");
		// ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext(
		// "classpath:spring/spring-context.xml");
		// OrderSpecMapper orderSpecMapper = (OrderSpecMapper) context.getBean("orderSpecMapper");
		// OrderDetailSummarySpecMapper orderDetailSummarySpecMapper=(OrderDetailSummarySpecMapper)
		// context.getBean("orderDetailSummarySpecMapper");
		// OrderDetailSummarySpecConsistMapper orderDetailSummarySpecConsistMapper=(OrderDetailSummarySpecConsistMapper)
		// context.getBean("orderDetailSummarySpecConsistMapper");
		// List<OrderDetailSummary>
		// orderDetailSummaryList=orderDetailSummaryMapper.findOrderDetailSummaryByOrderIds(orderIds);
		// Row row;
		// int rowNum=4;
		// int thirtListNum=0;
		// int firstListNum=0;
		// int listSize=0;
		//
		//
		//
		//
		//
		// Sheet secondSheet=workbook.createSheet();
		// Row firstRowOfSecondSheet=secondSheet.createRow(0);
		// firstRowOfSecondSheet.createCell(0).setCellValue("供应链拆解单");
		// region=new CellRangeAddress(0, 0, 0, 1);
		// secondSheet.addMergedRegion(region);
		// Row secondRowOfSecondSheet=secondSheet.createRow(1);
		// secondRowOfSecondSheet.createCell(0).setCellValue("供应链商品");
		// secondRowOfSecondSheet.createCell(1).setCellValue("数量");
		// firstRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		// secondRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		// secondRowOfSecondSheet.getCell(1).setCellStyle(setBorder);
		// secondSheet.setColumnWidth(0,8000);
		// secondSheet.setColumnWidth(1,5000);
		// StockoutSkuMapper stockoutSkuMapper=(StockoutSkuMapper) context.getBean("stockoutSkuMapper");
		// List<StockoutSku> skuList=stockoutSkuMapper.findStockoutSkuByOrderIds(orderIds);
		// if(CollectionUtils.isNotEmpty(skuList)) {
		// for(int i=0;i<skuList.size();i++) {
		// row=secondSheet.createRow(i+2);
		// row.createCell(0).setCellValue(skuList.get(i).getSkuName());
		// row.createCell(1).setCellValue(skuList.get(i).getAmount());
		// }
		// }
		//
		//
		//
		// Sheet thirdSheet=workbook.createSheet();
		// Row firstRowOfThirdSheet=thirdSheet.createRow(0);
		// firstRowOfThirdSheet.createCell(0).setCellValue("ERP订单");
		// region=new CellRangeAddress(0, 0, 0, 1);
		// thirdSheet.addMergedRegion(region);
		// Row secondRowOfThirdSheet=thirdSheet.createRow(1);
		// secondRowOfThirdSheet.createCell(0).setCellValue("订单商品");
		// secondRowOfThirdSheet.createCell(1).setCellValue("数量");
		// firstRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		// secondRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		// secondRowOfThirdSheet.getCell(1).setCellStyle(setBorder);
		// thirdSheet.setColumnWidth(0,10000);
		// thirdSheet.setColumnWidth(1,5000);
		// List<OrderDetailSummary> orderDetailList=orderDetailSummaryMapper.findOrderDetailSummary(orderIds);
		// if(CollectionUtils.isNotEmpty(orderDetailList)) {
		// for(int i=0;i<orderDetailList.size();i++) {
		// row=thirdSheet.createRow(i+2);
		// row.createCell(0).setCellValue(orderDetailList.get(i).getName());
		// row.createCell(1).setCellValue(orderDetailList.get(i).getAmount());
		// }
		// }
		//
		//
		//
		//
		//
		//
		//
		// if(CollectionUtils.isNotEmpty(orderDetailSummaryList)) {
		// for(int j=0;j<orderDetailSummaryList.size();j++) {
		// firstListNum=0;
		// List<OrderDetailSummarySpec>
		// orderDetailSummarySpecList=orderDetailSummarySpecMapper.findOrderDetailSummarySpecById(orderDetailSummaryList.get(j).getId());
		// if(CollectionUtils.isNotEmpty(orderDetailSummarySpecList)) {
		// for(OrderDetailSummarySpec odss:orderDetailSummarySpecList) {
		// List<OrderDetailSummarySpecConsist>
		// orderDetailSummarySpecConsistList=orderDetailSummarySpecConsistMapper.findOrderDetailSummarySpecConsist(odss.getSpecId(),odss.getSummaryId(),
		// odss.getBasinColor());
		// if(CollectionUtils.isNotEmpty(orderDetailSummarySpecConsistList)) {
		// for(int i=0;i<orderDetailSummarySpecConsistList.size();i++) {
		// row=sheet.createRow(i+rowNum);
		// row.createCell(0).setCellValue(orderDetailSummarySpecConsistList.get(i).getOrderNo());
		// sheet.getRow(i+rowNum).getCell(0).setCellStyle(setBorderLemonChiffon);;
		// row.createCell(5).setCellValue(orderDetailSummarySpecConsistList.get(i).getConsistSpecName());
		// row.createCell(6).setCellValue(orderDetailSummarySpecConsistList.get(i).getSalePrice());
		// row.createCell(7).setCellValue(orderDetailSummarySpecConsistList.get(i).getAmount());
		// row.createCell(8).setCellValue(orderDetailSummarySpecConsistList.get(i).getSkuName());
		// row.createCell(9).setCellValue(orderDetailSummarySpecConsistList.get(i).getAmount());
		//
		// //背景色
		// row.getCell(5).setCellStyle(setBorderOrange);
		// row.getCell(6).setCellStyle(setBorderOrange);
		// row.getCell(7).setCellStyle(setBorderOrange);
		// row.getCell(8).setCellStyle(setBorderLime);
		// row.getCell(9).setCellStyle(setBorderLime);
		//
		// }
		// firstListNum=firstListNum+orderDetailSummarySpecConsistList.size();
		// rowNum=rowNum+orderDetailSummarySpecConsistList.size();
		// thirtListNum=orderDetailSummarySpecConsistList.size();
		// listSize=listSize+orderDetailSummarySpecConsistList.size();
		// }
		// sheet.getRow(rowNum-thirtListNum).createCell(3).setCellValue(odss.getSpecName());
		// sheet.getRow(rowNum-thirtListNum).createCell(4).setCellValue(odss.getAmount());
		//
		// //设置养样式
		// sheet.getRow(rowNum-thirtListNum).getCell(3).setCellStyle(setBorderLightOrange);
		// sheet.getRow(rowNum-thirtListNum).getCell(4).setCellStyle(setBorderLightOrange);;
		//
		// region=new CellRangeAddress(rowNum-thirtListNum, rowNum-1,3, 3);
		// sheet.addMergedRegion(region);
		// region=new CellRangeAddress(rowNum-thirtListNum, rowNum-1,4, 4);
		// sheet.addMergedRegion(region);
		// }
		// }
		//
		// sheet.getRow(rowNum-firstListNum).createCell(1).setCellValue(orderDetailSummaryList.get(j).getAmount());
		// sheet.getRow(rowNum-firstListNum).createCell(2).setCellValue(orderDetailSummaryList.get(j).getName());
		// //设置样式
		// sheet.getRow(rowNum-firstListNum).getCell(1).setCellStyle(setBorderLemonChiffon);
		// sheet.getRow(rowNum-firstListNum).getCell(2).setCellStyle(setBorderLemonChiffon);
		//
		// region=new CellRangeAddress( rowNum-firstListNum, rowNum-1,1, 1);
		// sheet.addMergedRegion(region);
		// region=new CellRangeAddress(rowNum-firstListNum, rowNum-1,2, 2);
		// sheet.addMergedRegion(region);
		//
		//// if(j==0) {
		//// sheet.getRow(4).createCell(0).setCellValue(orderDetailSummaryList.get(0).getOrderNo());
		//// }
		//// else {
		//// if(!orderDetailSummaryList.get(j).getOrderNo().equals(orderDetailSummaryList.get(j-1).getOrderNo())) {
		//// sheet.getRow(rowNum-firstListNum).createCell(0).setCellValue(orderDetailSummaryList.get(j).getOrderNo());
		//// }
		//// }
		//
		// }
		// int a=4;
		// if(listSize>1) {
		// for(int i=0;i<listSize;i++) {
		// if(i>0) {
		// if(!sheet.getRow(i+4).getCell(0).getStringCellValue().equals(sheet.getRow(i+3).getCell(0).getStringCellValue()))
		// {
		// region=new CellRangeAddress(a,i+3,0,0);
		// sheet.addMergedRegion(region);
		// a=i+4;
		// }
		// }
		// }
		// region=new CellRangeAddress(a,listSize+3,0,0);
		// sheet.addMergedRegion(region);
		// }
		// }
		// workbook.setSheetName(0, "生产工艺单");
		// workbook.setSheetName(1, "供应链拆解单");
		// workbook.setSheetName(2, "ERP订单");

		Workbook workbook = new XSSFWorkbook();

		List<String> evidenceIds = new ArrayList<String>();
		evidenceIds.add("2886");
		evidenceIds.add("2888");
		evidenceIds.add("2889");
		evidenceIds.add("2891");
		String isSupplement = "1";

		List<ErpSkuDetail> all = null;

		Sheet sheet = workbook.createSheet("按项目");

		sheet.setMargin(XSSFSheet.BottomMargin, 0);

		sheet.setMargin(XSSFSheet.TopMargin, 0);

		XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLightOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderErp = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderSc = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLime = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLemonChiffon = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle autoCellStyle = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyle.setWrapText(true);
		autoCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		autoCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

		XSSFCellStyle autoCellStyleNoFont = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyleNoFont.setWrapText(true);
		autoCellStyleNoFont.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		autoCellStyleNoFont.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		autoCellStyleNoFont.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderRight(HSSFCellStyle.BORDER_THIN);

		XSSFCellStyle autoCellStyleAmount = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyleAmount.setWrapText(true);
		autoCellStyleAmount.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		autoCellStyleAmount.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		autoCellStyleAmount.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount.setBorderRight(HSSFCellStyle.BORDER_THIN);

		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);

		setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

		setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);

		// setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);

		setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

		setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 16);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		setBorder.setFont(font);
		setBorderErp.setFont(font);
		setBorderSc.setFont(font);

		XSSFFont firstFont = (XSSFFont) workbook.createFont();
		firstFont.setFontName("黑体");
		firstFont.setFontHeightInPoints((short) 10);// 设置字体大小
		firstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		autoCellStyle.setFont(firstFont);

		XSSFFont amountFont = (XSSFFont) workbook.createFont();
		amountFont.setFontName("黑体");
		amountFont.setFontHeightInPoints((short) 16);// 设置字体大小
		amountFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		autoCellStyleAmount.setFont(amountFont);

		CellRangeAddress region = null;

		// 第四行
		Row fourthRow = sheet.createRow(0);
		fourthRow.setHeightInPoints(24);// 行高

		fourthRow.createCell(0).setCellValue("项目名称");
		fourthRow.createCell(1).setCellValue("ERP订单商品");
		fourthRow.createCell(2).setCellValue("实际数量");
		fourthRow.createCell(3).setCellValue("ERP规格");
		fourthRow.createCell(4).setCellValue("数量");
		fourthRow.createCell(5).setCellValue("原料");
		fourthRow.createCell(6).setCellValue("数量");

		fourthRow.getCell(0).setCellStyle(autoCellStyle);
		fourthRow.getCell(1).setCellStyle(autoCellStyle);
		fourthRow.getCell(2).setCellStyle(autoCellStyle);
		fourthRow.getCell(3).setCellStyle(autoCellStyle);
		fourthRow.getCell(4).setCellStyle(autoCellStyle);
		fourthRow.getCell(5).setCellStyle(autoCellStyle);
		fourthRow.getCell(6).setCellStyle(autoCellStyle);
		sheet.setColumnWidth(0, 2743);
		sheet.setColumnWidth(1, 7095);
		sheet.setColumnWidth(2, 1207);
		sheet.setColumnWidth(3, 4279);
		sheet.setColumnWidth(4, 823);
		sheet.setColumnWidth(5, 4279);
		sheet.setColumnWidth(6, 823);

		Row row;
		Integer a = 1;
		Integer b = 1;
		Integer c = 1;
		Integer d = 1;

		// List<ErpSkuDetail> all = orderSpecService.findAll(evidenceIds);

		// productSpec-summaryId-orderAmount
		Map<String, Map<String, Map<String, Integer>>> stockroomIdProductSpecSummaryIdAmount = new HashMap<String, Map<String, Map<String, Integer>>>();

		// productSpec-summaryId-erpsku
		Map<String, Map.Entry<String, Set<ErpSpecSku>>> proSpecSummaryIdSkuMap = new HashMap<String, Map.Entry<String, Set<ErpSpecSku>>>();

		/**
		 * 以上为原始、中间数据结构
		 * 
		 * 以下为目标数据结构
		 */

		// productSpec集合
		Set<String> productSpecSet = new HashSet<>();

		// stockroom集合
		Set<String> stockroomIdSet = new HashSet<>();
		Map<String, String> stockroomIdNameMap = new HashMap<String, String>();

		Map<String, Set<String>> stockroomIdEidsMap = new HashMap<String, Set<String>>();

		// productSpec-productSpecName/picUrl
		Map<String, ErpSkuDetail> productSpecNameMap = new HashMap<String, ErpSkuDetail>();

		// stockroomId-productSpec-orderAmount
		Map<String, Map<String, Integer>> stockroomIdProductSpecAmountMap = new HashMap<String, Map<String, Integer>>();

		// productSpec-erpsku
		Map<String, Set<ErpSpecSku>> proSpecSkuMap = new HashMap<String, Set<ErpSpecSku>>();

		if (CollectionUtils.isNotEmpty(all)) {
			for (ErpSkuDetail erpSkuDetail : all) {

				addProductSpecSummaryIdAmount2(stockroomIdProductSpecSummaryIdAmount, erpSkuDetail);
				addProductSpecSkuAmount(proSpecSummaryIdSkuMap, erpSkuDetail);
				productSpecSet.add(erpSkuDetail.getProductSpecGroupKey());
				stockroomIdSet.add(erpSkuDetail.getStockroomId());
				stockroomIdNameMap.putIfAbsent(erpSkuDetail.getStockroomId(), erpSkuDetail.getStockroomName());
				productSpecNameMap.putIfAbsent(erpSkuDetail.getProductSpecGroupKey(), erpSkuDetail);

				Set<String> eidSet = stockroomIdEidsMap.get(erpSkuDetail.getStockroomId());
				if (CollectionUtils.isEmpty(eidSet)) {
					eidSet = new HashSet<String>();
				}
				eidSet.add(erpSkuDetail.getEvidenceId());
				stockroomIdEidsMap.put(erpSkuDetail.getStockroomId(), eidSet);
			}
		}

		for (String productSpecKey : productSpecSet) {
			proSpecSkuMap.put(productSpecKey, proSpecSummaryIdSkuMap.get(productSpecKey).getValue());
		}

		for (String stockroomIdKey : stockroomIdSet) {

			Map<String, Map<String, Integer>> stockProSpecSummaryIdsAmount = stockroomIdProductSpecSummaryIdAmount
					.get(stockroomIdKey);

			Map<String, Integer> productSpecAmountMap = stockroomIdProductSpecAmountMap.get(stockroomIdKey);

			if (productSpecAmountMap == null) {
				productSpecAmountMap = new HashMap<String, Integer>();
				stockroomIdProductSpecAmountMap.put(stockroomIdKey, productSpecAmountMap);
			}

			for (String productSpeckey : stockProSpecSummaryIdsAmount.keySet()) {
				Integer total = 0;
				for (Integer summaryIdAmount : stockProSpecSummaryIdsAmount.get(productSpeckey).values()) {
					total += summaryIdAmount;
				}
				productSpecAmountMap.put(productSpeckey, total);
			}

		}
		if (!stockroomIdProductSpecAmountMap.isEmpty()) {
			for (String key1 : stockroomIdProductSpecAmountMap.keySet()) {
				Map<String, Integer> map1 = stockroomIdProductSpecAmountMap.get(key1);
				if (!map1.isEmpty()) {
					for (String key2 : map1.keySet()) {
						ErpSkuDetail erpSkuDetail = productSpecNameMap.get(key2);
						Set<ErpSpecSku> erpSpecSkuSet = proSpecSkuMap.get(key2);
						if (CollectionUtils.isNotEmpty(erpSpecSkuSet)) {
							for (ErpSpecSku es : erpSpecSkuSet) {
								row = sheet.createRow(a);

								row.createCell(0);
								row.createCell(1);
								row.createCell(2);

								row.createCell(3).setCellValue(es.getSpecName());
								row.createCell(4).setCellValue(es.getAmount());
								row.createCell(5).setCellValue(es.getSkuName());
								row.createCell(6).setCellValue(es.getSkuAmount() * es.getAmount());

								row.getCell(0).setCellStyle(autoCellStyleNoFont);
								row.getCell(1).setCellStyle(autoCellStyleNoFont);
								row.getCell(2).setCellStyle(autoCellStyleNoFont);
								row.getCell(3).setCellStyle(autoCellStyleNoFont);
								row.getCell(4).setCellStyle(autoCellStyleAmount);
								row.getCell(5).setCellStyle(autoCellStyleNoFont);
								row.getCell(6).setCellStyle(autoCellStyleAmount);

								a = a + 1;
							}
							c = c + erpSpecSkuSet.size();
							sheet.getRow(b).createCell(1).setCellValue(erpSkuDetail.getFullName());
							sheet.getRow(b).createCell(2).setCellValue(map1.get(key2));

							sheet.getRow(b).getCell(1).setCellStyle(autoCellStyleNoFont);
							sheet.getRow(b).getCell(2).setCellStyle(autoCellStyleAmount);

							region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 1, 1);
							sheet.addMergedRegion(region);
							region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 2, 2);
							sheet.addMergedRegion(region);

							b = b + erpSpecSkuSet.size();
						}

					}
				}

				String eids = "";
				Set<String> eidSet = stockroomIdEidsMap.get(key1);
				if (CollectionUtils.isNotEmpty(eidSet)) {
					for (String eid : eidSet) {
						eids = eids + "," + eid;
					}
				}

				sheet.getRow(d).createCell(0).setCellValue(stockroomIdNameMap.get(key1) + "\n" + eids);
				region = new CellRangeAddress(d, c - 1, 0, 0);
				sheet.addMergedRegion(region);

				sheet.getRow(d).getCell(0).setCellStyle(autoCellStyleNoFont);
				d = c;
			}
		}

		try {
			String name = "workTime_excel";
			File fileBatch = new File("E:/");
			if (!fileBatch.exists()) {
				fileBatch.mkdir();
			}
			// 默认导出到E盘下
			FileOutputStream out = null;
			try {
				out = new FileOutputStream(fileBatch + "/" + name + ".xls");
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			workbook.write(out);
			out.flush();
			out.close();// 关闭流
		} finally {

		}
		System.out.println("222222");
	}

	private static void createSecondSheet(Workbook workbook, List<String> evidenceIds, List<ErpSkuDetail> all) {
		Sheet sheet = workbook.createSheet("按项目");

		XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLightOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderErp = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderSc = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLime = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLemonChiffon = (XSSFCellStyle) workbook.createCellStyle();

		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);

		setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

		setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);

		// setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);

		setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

		setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 16);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		setBorder.setFont(font);
		setBorderErp.setFont(font);
		setBorderSc.setFont(font);

		// 创建第一行表头
		Row firstRow = sheet.createRow(0);
		firstRow.createCell(0).setCellValue("南京植百汇智能科技服务有限公司");

		CellRangeAddress region = null;
		region = new CellRangeAddress(0, 0, 0, 7);
		sheet.addMergedRegion(region);

		Row secondRow = sheet.createRow(1);
		secondRow.createCell(0).setCellValue("生产工艺单");
		region = new CellRangeAddress(1, 1, 0, 7);
		sheet.addMergedRegion(region);

		// 第二行
		// Row secondRow = sheet.createRow(1);
		// secondRow.createCell(0).setCellValue("ERP侧");
		// secondRow.createCell(5).setCellValue("供应链侧");
		// region = new CellRangeAddress(1, 1, 0, 4);
		// sheet.addMergedRegion(region);
		// region = new CellRangeAddress(1, 1, 5, 6);
		// sheet.addMergedRegion(region);

		// 第三行
		Row thirdRow = sheet.createRow(2);
		thirdRow.createCell(0).setCellValue("原始订单");
		thirdRow.createCell(4).setCellValue("规格分解");
		thirdRow.createCell(6).setCellValue("SKU原料");
		region = new CellRangeAddress(2, 2, 0, 3);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 4, 5);
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(2, 2, 6, 7);
		sheet.addMergedRegion(region);

		// 第四行
		Row fourthRow = sheet.createRow(3);
		fourthRow.createCell(0).setCellValue("项目名称");
		fourthRow.createCell(1).setCellValue("图片");
		fourthRow.createCell(2).setCellValue("ERP订单商品");
		fourthRow.createCell(3).setCellValue("实际数量");
		fourthRow.createCell(4).setCellValue("ERP规格");
		fourthRow.createCell(5).setCellValue("数量");
		fourthRow.createCell(6).setCellValue("原料");
		fourthRow.createCell(7).setCellValue("数量");

		// 样式设置
		firstRow.getCell(0).setCellStyle(setBorder);
		secondRow.getCell(0).setCellStyle(setBorder);
		// secondRow.getCell(0).setCellStyle(setBorderErp);
		// secondRow.getCell(5).setCellStyle(setBorderSc);
		thirdRow.getCell(0).setCellStyle(setBorderErp);
		thirdRow.getCell(4).setCellStyle(setBorderErp);
		thirdRow.getCell(6).setCellStyle(setBorderSc);
		fourthRow.getCell(0).setCellStyle(setBorderErp);
		fourthRow.getCell(1).setCellStyle(setBorderErp);
		fourthRow.getCell(2).setCellStyle(setBorderErp);
		fourthRow.getCell(3).setCellStyle(setBorderErp);
		fourthRow.getCell(4).setCellStyle(setBorderErp);
		fourthRow.getCell(5).setCellStyle(setBorderSc);
		fourthRow.getCell(6).setCellStyle(setBorderSc);
		fourthRow.getCell(7).setCellStyle(setBorderSc);
		sheet.setColumnWidth(0, 5000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 20000);
		sheet.setColumnWidth(3, 1000);
		sheet.setColumnWidth(4, 15000);
		sheet.setColumnWidth(5, 1000);
		sheet.setColumnWidth(6, 20000);
		sheet.setColumnWidth(7, 1000);

		Row row;
		Integer a = 4;
		Integer b = 4;
		Integer c = 4;
		Integer d = 4;

		// List<ErpSkuDetail> all = orderSpecService.findAll(evidenceIds);

		// productSpec-summaryId-orderAmount
		Map<String, Map<String, Map<String, Integer>>> stockroomIdProductSpecSummaryIdAmount = new HashMap<String, Map<String, Map<String, Integer>>>();

		// productSpec-summaryId-erpsku
		Map<String, Map.Entry<String, Set<ErpSpecSku>>> proSpecSummaryIdSkuMap = new HashMap<String, Map.Entry<String, Set<ErpSpecSku>>>();

		/**
		 * 以上为原始、中间数据结构
		 * 
		 * 以下为目标数据结构
		 */

		// productSpec集合
		Set<String> productSpecSet = new HashSet<>();

		// stockroom集合
		Set<String> stockroomIdSet = new HashSet<>();
		Map<String, String> stockroomIdNameMap = new HashMap<String, String>();

		Map<String, Set<String>> stockroomIdEidsMap = new HashMap<String, Set<String>>();

		// productSpec-productSpecName/picUrl
		Map<String, ErpSkuDetail> productSpecNameMap = new HashMap<String, ErpSkuDetail>();

		// stockroomId-productSpec-orderAmount
		Map<String, Map<String, Integer>> stockroomIdProductSpecAmountMap = new HashMap<String, Map<String, Integer>>();

		// productSpec-erpsku
		Map<String, Set<ErpSpecSku>> proSpecSkuMap = new HashMap<String, Set<ErpSpecSku>>();

		if (CollectionUtils.isNotEmpty(all)) {
			for (ErpSkuDetail erpSkuDetail : all) {

				addProductSpecSummaryIdAmount2(stockroomIdProductSpecSummaryIdAmount, erpSkuDetail);
				addProductSpecSkuAmount(proSpecSummaryIdSkuMap, erpSkuDetail);
				productSpecSet.add(erpSkuDetail.getProductSpecGroupKey());
				stockroomIdSet.add(erpSkuDetail.getStockroomId());
				stockroomIdNameMap.putIfAbsent(erpSkuDetail.getStockroomId(), erpSkuDetail.getStockroomName());
				productSpecNameMap.putIfAbsent(erpSkuDetail.getProductSpecGroupKey(), erpSkuDetail);

				Set<String> eidSet = stockroomIdEidsMap.get(erpSkuDetail.getStockroomId());
				if (CollectionUtils.isEmpty(eidSet)) {
					eidSet = new HashSet<String>();
				}
				eidSet.add(erpSkuDetail.getEvidenceId());
				stockroomIdEidsMap.put(erpSkuDetail.getStockroomId(), eidSet);
			}
		}

		for (String productSpecKey : productSpecSet) {
			proSpecSkuMap.put(productSpecKey, proSpecSummaryIdSkuMap.get(productSpecKey).getValue());
		}

		for (String stockroomIdKey : stockroomIdSet) {

			Map<String, Map<String, Integer>> stockProSpecSummaryIdsAmount = stockroomIdProductSpecSummaryIdAmount
					.get(stockroomIdKey);

			Map<String, Integer> productSpecAmountMap = stockroomIdProductSpecAmountMap.get(stockroomIdKey);

			if (productSpecAmountMap == null) {
				productSpecAmountMap = new HashMap<String, Integer>();
				stockroomIdProductSpecAmountMap.put(stockroomIdKey, productSpecAmountMap);
			}

			for (String productSpeckey : stockProSpecSummaryIdsAmount.keySet()) {
				Integer total = 0;
				for (Integer summaryIdAmount : stockProSpecSummaryIdsAmount.get(productSpeckey).values()) {
					total += summaryIdAmount;
				}
				productSpecAmountMap.put(productSpeckey, total);
			}

		}
		if (!stockroomIdProductSpecAmountMap.isEmpty()) {
			for (String key1 : stockroomIdProductSpecAmountMap.keySet()) {
				Map<String, Integer> map1 = stockroomIdProductSpecAmountMap.get(key1);
				if (!map1.isEmpty()) {
					for (String key2 : map1.keySet()) {
						ErpSkuDetail erpSkuDetail = productSpecNameMap.get(key2);
						Set<ErpSpecSku> erpSpecSkuSet = proSpecSkuMap.get(key2);
						if (CollectionUtils.isNotEmpty(erpSpecSkuSet)) {
							for (ErpSpecSku es : erpSpecSkuSet) {
								row = sheet.createRow(a);
								sheet.getRow(a).setHeight((short) 1200);
								row.createCell(4).setCellValue(es.getSpecName());
								row.createCell(5).setCellValue(es.getAmount());
								row.createCell(6).setCellValue(es.getSkuName());
								row.createCell(7).setCellValue(es.getSkuAmount() * es.getAmount());
								a = a + 1;
							}
							c = c + erpSpecSkuSet.size();
							XSSFCell cell = (XSSFCell) sheet.getRow(b).createCell(1);
							// try {
							// ExcelUtil.addimg((XSSFSheet) sheet, (XSSFWorkbook) workbook, erpSkuDetail.getImageUrl(),
							// (short) cell.getColumnIndex(), (short) (cell.getColumnIndex() + 1),
							// sheet.getLastRowNum(), (short) sheet.getLastRowNum() + 1);
							// } catch (Exception e) {
							// e.printStackTrace();
							// }
							sheet.getRow(b).createCell(2).setCellValue(erpSkuDetail.getFullName());
							sheet.getRow(b).createCell(3).setCellValue(map1.get(key2));

							region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 1, 1);
							sheet.addMergedRegion(region);
							region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 2, 2);
							sheet.addMergedRegion(region);
							region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 3, 3);
							sheet.addMergedRegion(region);

							b = b + erpSpecSkuSet.size();
						}

					}
				}

				String eids = "";
				Set<String> eidSet = stockroomIdEidsMap.get(key1);
				if (CollectionUtils.isNotEmpty(eidSet)) {
					for (String eid : eidSet) {
						eids = eids + "," + eid;
					}
				}

				sheet.getRow(d).createCell(0).setCellValue(stockroomIdNameMap.get(key1) + "\n" + eids);
				region = new CellRangeAddress(d, c - 1, 0, 0);
				sheet.addMergedRegion(region);
				d = c;
			}
		}
	}

	/**
	 * 
	 * productSpec下某个serverSummaryId的erpSpecSku
	 * 
	 * @param proSpecSkuMap
	 * @param erpSkuDetail
	 * @author: Elvin Zhao - 279152260@qq.com 
	 * @date: Created on 2019年9月28日 下午3:49:49
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private static void addProductSpecSkuAmount(Map<String, Map.Entry<String, Set<ErpSpecSku>>> proSpecSummaryIdSkuMap,
			ErpSkuDetail erpSkuDetail) {
		String productSpecGroupKey = erpSkuDetail.getProductSpecGroupKey();
		Map.Entry<String, Set<ErpSpecSku>> summaryIdErpSpecSku = proSpecSummaryIdSkuMap.get(productSpecGroupKey);

		Set<ErpSpecSku> erpSpecSkuSet = null;

		if (summaryIdErpSpecSku == null) {

			erpSpecSkuSet = new HashSet<ErpSpecSku>();
			erpSpecSkuSet.add(erpSkuDetail.getErpSpecSku());
			summaryIdErpSpecSku = new AbstractMap.SimpleEntry(erpSkuDetail.getServiceTypeIdSummaryIdGroupKey(),
					erpSpecSkuSet);
			proSpecSummaryIdSkuMap.put(productSpecGroupKey, summaryIdErpSpecSku);
		} else {

			if (StringUtils.equals(summaryIdErpSpecSku.getKey(), erpSkuDetail.getServiceTypeIdSummaryIdGroupKey())) {
				erpSpecSkuSet = summaryIdErpSpecSku.getValue();
				erpSpecSkuSet.add(erpSkuDetail.getErpSpecSku());
			} else {
				return;
			}

		}

	}

	/**
	 * 累积数量用，去重serverSummaryId
	 * 
	 * @param productSpecSummaryIdAmount
	 * @param erpSkuDetail
	 * @author: Elvin Zhao - 279152260@qq.com 
	 * @date: Created on 2019年9月28日 下午3:22:35
	 */
	private static void addProductSpecSummaryIdAmount(Map<String, Map<String, Integer>> productSpecSummaryIdAmount,
			ErpSkuDetail erpSkuDetail) {
		String productSpecGroupKey = erpSkuDetail.getProductSpecGroupKey();
		Map<String, Integer> summaryIdsAmount = productSpecSummaryIdAmount.get(productSpecGroupKey);

		if (summaryIdsAmount == null) {
			summaryIdsAmount = new HashMap<String, Integer>();
			productSpecSummaryIdAmount.put(productSpecGroupKey, summaryIdsAmount);
		}
		summaryIdsAmount.putIfAbsent(erpSkuDetail.getServiceTypeIdSummaryIdGroupKey(), erpSkuDetail.getOrderAmount());

	}

	/**
	 * 累积数量用，去重serverSummaryId
	 * 
	 * @param productSpecSummaryIdAmount
	 * @param erpSkuDetail
	 * @author: Elvin Zhao - 279152260@qq.com 
	 * @date: Created on 2019年9月28日 下午3:22:35
	 */
	private static void addProductSpecSummaryIdAmount2(
			Map<String, Map<String, Map<String, Integer>>> allProductSpecSummaryIdAmount, ErpSkuDetail erpSkuDetail) {

		String stockroomId = erpSkuDetail.getStockroomId();
		String productSpecGroupKey = erpSkuDetail.getProductSpecGroupKey();

		Map<String, Map<String, Integer>> stockProSpecSummaryIdsAmount = allProductSpecSummaryIdAmount.get(stockroomId);
		Map<String, Integer> summaryIdsAmount = null;

		if (stockProSpecSummaryIdsAmount == null) {
			// summaryId层
			summaryIdsAmount = new HashMap<String, Integer>();
			summaryIdsAmount.put(erpSkuDetail.getServiceTypeIdSummaryIdGroupKey(), erpSkuDetail.getOrderAmount());

			// productSpec-summaryId层
			stockProSpecSummaryIdsAmount = new HashMap<String, Map<String, Integer>>();
			stockProSpecSummaryIdsAmount.put(productSpecGroupKey, summaryIdsAmount);

			// stockroomId-productSpec-summaryId层
			allProductSpecSummaryIdAmount.put(stockroomId, stockProSpecSummaryIdsAmount);

		} else {

			summaryIdsAmount = stockProSpecSummaryIdsAmount.get(productSpecGroupKey);
			if (summaryIdsAmount == null) {
				summaryIdsAmount = new HashMap<String, Integer>();
				stockProSpecSummaryIdsAmount.put(productSpecGroupKey, summaryIdsAmount);
			}
			summaryIdsAmount.putIfAbsent(erpSkuDetail.getServiceTypeIdSummaryIdGroupKey(),
					erpSkuDetail.getOrderAmount());

		}
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#exportCode(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年10月10日 上午9:51:45
	 */
	@Override
	public Workbook exportCode(String evidenceId) {
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();
		Row row1 = sheet.createRow(0);
		row1.createCell(0).setCellValue("name");
		row1.createCell(1).setCellValue("code");
		List<ErpResPrinter> list = stockoutErpResourceService.findErpResByEid(evidenceId);
		Row row;
		if (CollectionUtils.isNotEmpty(list)) {
			for (int i = 0; i < list.size(); i++) {
				row = sheet.createRow(i + 1);
				row.createCell(0).setCellValue(list.get(i).getName());
				row.createCell(1).setCellValue(list.get(i).getCode());
			}
		}

		sheet.setColumnWidth(0, 20000);
		sheet.setColumnWidth(1, 5000);
		return workbook;
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#exportExcelAllCode(java.util.List)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年10月17日 上午10:13:56
	 */
	@Override
	public Workbook exportExcelAllCode(List<String> evidenceIds) {
		String url = Global.getConfig("wx.qrcode.url");
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();
		Row row1 = sheet.createRow(0);
		row1.createCell(0).setCellValue("name");
		row1.createCell(1).setCellValue("code");
		List<ErpResPrinter> list = stockoutErpResourceService.findErpResByEids(evidenceIds);
		Row row;
		int num = 1;
		if (CollectionUtils.isNotEmpty(list)) {
			for (int i = 0; i < list.size(); i++) {
				if (i == 0) {
					row = sheet.createRow(i + 1);
					row.createCell(0).setCellValue(list.get(i).getCustomerName());
					row = sheet.createRow(i + 2);
					row.createCell(0).setCellValue(list.get(i).getName());
					row.createCell(1).setCellValue(url + list.get(i).getCode());
					num = num + 1;
				} else {
					if (!list.get(i).getEvidenceId().equals(list.get(i - 1).getEvidenceId())) {
						row = sheet.createRow(i + num);
						row.createCell(0).setCellValue(list.get(i).getCustomerName());
						num = num + 1;
						row = sheet.createRow(i + num);
						row.createCell(0).setCellValue(list.get(i).getName());
						row.createCell(1).setCellValue(url + list.get(i).getCode());
					} else {
						row = sheet.createRow(i + num);
						row.createCell(0).setCellValue(list.get(i).getName());
						row.createCell(1).setCellValue(url + list.get(i).getCode());
					}
				}

			}
		}
		sheet.setColumnWidth(0, 20000);
		sheet.setColumnWidth(1, 5000);
		return workbook;
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#exportExcelAllCodeByOrderIds(java.util.List)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2022年3月1日 下午6:08:01
	 */
	@Override
	public Workbook exportExcelAllCodeByOrderIds(List<String> orderIds) {
		String url = Global.getConfig("wx.qrcode.url");
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet();
		Row row1 = sheet.createRow(0);
		row1.createCell(0).setCellValue("name");
		row1.createCell(1).setCellValue("code");
		List<ErpResPrinter> list = stockoutErpResourceService.findErpResByOrderIds(orderIds);
		Row row;
		int num = 1;
		if (CollectionUtils.isNotEmpty(list)) {
			for (int i = 0; i < list.size(); i++) {
				if (i == 0) {
					row = sheet.createRow(i + 1);
					row.createCell(0).setCellValue(list.get(i).getStockroomName());
					row = sheet.createRow(i + 2);
					row.createCell(0).setCellValue(list.get(i).getName());
					row.createCell(1).setCellValue(url + list.get(i).getCode());
					num = num + 1;
				} else {
					if (!list.get(i).getStockroomId().equals(list.get(i - 1).getStockroomId())) {
						row = sheet.createRow(i + num);
						row.createCell(0).setCellValue(list.get(i).getStockroomName());
						num = num + 1;
						row = sheet.createRow(i + num);
						row.createCell(0).setCellValue(list.get(i).getName());
						row.createCell(1).setCellValue(url + list.get(i).getCode());
					} else {
						row = sheet.createRow(i + num);
						row.createCell(0).setCellValue(list.get(i).getName());
						row.createCell(1).setCellValue(url + list.get(i).getCode());
					}
				}

			}
		}
		sheet.setColumnWidth(0, 20000);
		sheet.setColumnWidth(1, 5000);
		return workbook;
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findSummarySkuByCustomerId(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年10月18日 下午3:21:56
	 */
	@Override
	public List<OrderSpec> findSummarySkuByCustomerId(String customerId) {
		return orderSpecService.findSummarySkuByCustomerId(customerId);
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findRepairSkuByCustomerId(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年10月18日 下午4:57:35
	 */
	@Override
	public List<OrderSpec> findRepairSkuByCustomerId(String customerId) {
		return orderSpecService.findRepairSkuByCustomerId(customerId);
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findOrderDetailSkuByCustomerId(java.lang.String)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2019年10月18日 下午5:06:32
	 */
	@Override
	public List<OrderSpec> findOrderDetailSkuByCustomerId(String customerId) {
		return orderSpecService.findOrderDetailSkuByCustomerId(customerId);
	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#createWorkbookByEvidenceIdsNew(java.util.List)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2021年2月7日 上午11:37:57
	 */
	@Override
	public Workbook createWorkbookByEvidenceIdsNew(List<String> evidenceIds, String isSupplement) {
		Workbook workbook = new XSSFWorkbook();

		List<ErpSkuDetail> all = orderSpecService.findAll(evidenceIds, isSupplement);

		/**
		 * sheet1 按项目合并
		 */
		createSecondSheetNew(workbook, evidenceIds, all);

		/**
		 * 盆器sku详情
		 */
		createFlowerpotSheetNew(workbook, evidenceIds, isSupplement);

		XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLightOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderErp = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderSc = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLime = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLemonChiffon = (XSSFCellStyle) workbook.createCellStyle();

		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);

		setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

		setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);

		// setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);

		setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

		setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 16);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		setBorder.setFont(font);
		setBorderErp.setFont(font);
		setBorderSc.setFont(font);

		CellRangeAddress region = null;

		Row row;

		/**
		 * sheet3 领料单
		 */
		Sheet secondSheet = workbook.createSheet("领料单");
		Row firstRowOfSecondSheet = secondSheet.createRow(0);
		firstRowOfSecondSheet.createCell(0).setCellValue("领料单");
		region = new CellRangeAddress(0, 0, 0, 1);
		secondSheet.addMergedRegion(region);
		Row secondRowOfSecondSheet = secondSheet.createRow(1);
		secondRowOfSecondSheet.createCell(0).setCellValue("原料");
		secondRowOfSecondSheet.createCell(1).setCellValue("数量");
		firstRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfSecondSheet.getCell(1).setCellStyle(setBorder);
		secondSheet.setColumnWidth(0, 20000);
		secondSheet.setColumnWidth(1, 1000);
		List<StockoutSku> skuList = stockoutSkuService.findSkuByEvidenceIds(evidenceIds);
		if (CollectionUtils.isNotEmpty(skuList)) {
			for (int i = 0; i < skuList.size(); i++) {
				row = secondSheet.createRow(i + 2);
				row.createCell(0).setCellValue(skuList.get(i).getSkuName());
				row.createCell(1).setCellValue(skuList.get(i).getAmount());
			}
		}

		/**
		 * sheet2 按品种合并
		 */
		// Sheet sheet = workbook.createSheet("按品种");
		//
		// // 创建第一行表头
		// Row firstRow = sheet.createRow(0);
		// firstRow.createCell(0).setCellValue("南京植百汇智能科技服务有限公司");
		//
		// region = new CellRangeAddress(0, 0, 0, 6);
		// sheet.addMergedRegion(region);
		//
		// Row secondRow = sheet.createRow(1);
		// secondRow.createCell(0).setCellValue("生产工艺单");
		// region = new CellRangeAddress(1, 1, 0, 6);
		// sheet.addMergedRegion(region);
		//
		// // 第二行
		// // Row secondRow = sheet.createRow(1);
		// // secondRow.createCell(0).setCellValue("ERP侧");
		// // secondRow.createCell(5).setCellValue("供应链侧");
		// // region = new CellRangeAddress(1, 1, 0, 4);
		// // sheet.addMergedRegion(region);
		// // region = new CellRangeAddress(1, 1, 5, 6);
		// // sheet.addMergedRegion(region);
		//
		// // 第三行
		// Row thirdRow = sheet.createRow(2);
		// thirdRow.createCell(0).setCellValue("原始订单");
		// thirdRow.createCell(3).setCellValue("规格分解");
		// thirdRow.createCell(5).setCellValue("SKU原料");
		// region = new CellRangeAddress(2, 2, 0, 2);
		// sheet.addMergedRegion(region);
		// region = new CellRangeAddress(2, 2, 3, 4);
		// sheet.addMergedRegion(region);
		// region = new CellRangeAddress(2, 2, 5, 6);
		// sheet.addMergedRegion(region);
		//
		// // 第四行
		// Row fourthRow = sheet.createRow(3);
		// fourthRow.createCell(0).setCellValue("图片");
		// fourthRow.createCell(1).setCellValue("ERP订单商品");
		// fourthRow.createCell(2).setCellValue("实际数量");
		// fourthRow.createCell(3).setCellValue("ERP规格");
		// fourthRow.createCell(4).setCellValue("数量");
		// fourthRow.createCell(5).setCellValue("原料");
		// fourthRow.createCell(6).setCellValue("数量");
		//
		// // 样式设置
		// firstRow.getCell(0).setCellStyle(setBorder);
		// secondRow.getCell(0).setCellStyle(setBorder);
		// // secondRow.getCell(0).setCellStyle(setBorderErp);
		// // secondRow.getCell(5).setCellStyle(setBorderSc);
		// thirdRow.getCell(0).setCellStyle(setBorderErp);
		// thirdRow.getCell(3).setCellStyle(setBorderErp);
		// thirdRow.getCell(5).setCellStyle(setBorderSc);
		// fourthRow.getCell(0).setCellStyle(setBorderErp);
		// fourthRow.getCell(1).setCellStyle(setBorderErp);
		// fourthRow.getCell(2).setCellStyle(setBorderErp);
		// fourthRow.getCell(3).setCellStyle(setBorderErp);
		// fourthRow.getCell(4).setCellStyle(setBorderErp);
		// fourthRow.getCell(5).setCellStyle(setBorderSc);
		// fourthRow.getCell(6).setCellStyle(setBorderSc);
		// sheet.setColumnWidth(0, 5000);
		// sheet.setColumnWidth(1, 20000);
		// sheet.setColumnWidth(2, 1000);
		// sheet.setColumnWidth(3, 15000);
		// sheet.setColumnWidth(4, 1000);
		// sheet.setColumnWidth(5, 20000);
		// sheet.setColumnWidth(6, 1000);
		//
		// int a = 4;
		// int b = 4;
		//
		// // productSpec-summaryId-orderAmount
		// Map<String, Map<String, Integer>> productSpecSummaryIdAmount = new HashMap<String, Map<String, Integer>>();
		//
		// // productSpec-summaryId-erpsku
		// Map<String, Map.Entry<String, Set<ErpSpecSku>>> proSpecSummaryIdSkuMap = new HashMap<String,
		// Map.Entry<String, Set<ErpSpecSku>>>();
		//
		// /**
		// * 以上为原始、中间数据结构
		// *
		// * 以下为目标数据结构
		// */
		//
		// // productSpec集合
		// Set<String> productSpecSet = new HashSet<>();
		//
		// // productSpec-productSpecName
		// Map<String, String> productSpecNameMap = new HashMap<String, String>();
		//
		// // productSpec-orderAmount
		// Map<String, Integer> productSpecAmountMap = new HashMap<String, Integer>();
		//
		// // productSpec-erpsku
		// Map<String, Set<ErpSpecSku>> proSpecSkuMap = new HashMap<String, Set<ErpSpecSku>>();
		//
		// if (CollectionUtils.isNotEmpty(all)) {
		// for (ErpSkuDetail erpSkuDetail : all) {
		//
		// addProductSpecSummaryIdAmount(productSpecSummaryIdAmount, erpSkuDetail);
		// addProductSpecSkuAmount(proSpecSummaryIdSkuMap, erpSkuDetail);
		// productSpecSet.add(erpSkuDetail.getProductSpecGroupKey());
		// productSpecNameMap.putIfAbsent(erpSkuDetail.getProductSpecGroupKey(), erpSkuDetail.getFullName());
		// }
		// }
		//
		// for (String productSpeckey : productSpecSet) {
		// Integer total = 0;
		// for (Integer summaryIdAmount : productSpecSummaryIdAmount.get(productSpeckey).values()) {
		// total += summaryIdAmount;
		// }
		// productSpecAmountMap.put(productSpeckey, total);
		//
		// proSpecSkuMap.put(productSpeckey, proSpecSummaryIdSkuMap.get(productSpeckey).getValue());
		//
		// }
		//
		// // Iterator<Entry<String, Set<ErpSpecSku>>> it = proSpecSkuMap.entrySet().iterator();
		// // while (it.hasNext()) {
		// // Entry<String, Set<ErpSpecSku>> entry = it.next();
		// // Set<ErpSpecSku> erpSpecSkuSet = entry.getValue();
		// // for (ErpSpecSku s : erpSpecSkuSet) {
		// // System.out.println(s.getSpecId());
		// // }
		// // }
		//
		// if (!productSpecAmountMap.isEmpty()) {
		// for (String key : productSpecAmountMap.keySet()) {
		// Set<ErpSpecSku> erpSpecSkuSet = proSpecSkuMap.get(key);
		// if (CollectionUtils.isNotEmpty(erpSpecSkuSet)) {
		// ErpSpecSku erpSpecSku = new ErpSpecSku();
		// for (ErpSpecSku es : erpSpecSkuSet) {
		// erpSpecSku = es;
		// row = sheet.createRow(a);
		// sheet.getRow(a).setHeight((short) 1200);
		// row.createCell(3).setCellValue(es.getSpecName());
		// row.createCell(4).setCellValue(es.getSkuAmount() * es.getAmount());
		// row.createCell(5).setCellValue(es.getSkuName());
		// row.createCell(6).setCellValue(es.getSkuAmount() * es.getAmount());
		//
		// a = a + 1;
		// }
		// sheet.getRow(b).createCell(1).setCellValue(productSpecNameMap.get(key));
		// sheet.getRow(b).createCell(2).setCellValue(productSpecAmountMap.get(key));
		//
		// XSSFCell cell = (XSSFCell) sheet.getRow(b).createCell(0);
		// // try {
		// // ExcelUtil.addimg((XSSFSheet) sheet, (XSSFWorkbook) workbook, erpSpecSku.getImageUrl(),
		// // (short) cell.getColumnIndex(), (short) (cell.getColumnIndex() + 1),
		// // sheet.getLastRowNum(), (short) sheet.getLastRowNum() + 1);
		// // } catch (Exception e) {
		// // e.printStackTrace();
		// // }
		// region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 1, 1);
		// sheet.addMergedRegion(region);
		// region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 2, 2);
		// sheet.addMergedRegion(region);
		// region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 0, 0);
		// sheet.addMergedRegion(region);
		// b = b + erpSpecSkuSet.size();
		// }
		//
		// }
		//
		// }

		/**
		 * sheet4 ERP商品清单
		 */
		Sheet thirdSheet = workbook.createSheet("ERP商品清单");
		Row firstRowOfThirdSheet = thirdSheet.createRow(0);
		firstRowOfThirdSheet.createCell(0).setCellValue("ERP商品清单");
		region = new CellRangeAddress(0, 0, 0, 2);
		thirdSheet.addMergedRegion(region);
		Row secondRowOfThirdSheet = thirdSheet.createRow(1);
		secondRowOfThirdSheet.createCell(0).setCellValue("订单商品");
		secondRowOfThirdSheet.createCell(1).setCellValue("数量");
		secondRowOfThirdSheet.createCell(2).setCellValue("图片");
		firstRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(0).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(1).setCellStyle(setBorder);
		secondRowOfThirdSheet.getCell(2).setCellStyle(setBorder);
		thirdSheet.setColumnWidth(0, 20000);
		thirdSheet.setColumnWidth(1, 1000);
		thirdSheet.setColumnWidth(2, 3000);
		List<OrderDetailSummary> orderDetailList = orderDetailSummaryService.findErpSummary(evidenceIds);
		if (CollectionUtils.isNotEmpty(orderDetailList)) {
			for (int i = 0; i < orderDetailList.size(); i++) {
				row = thirdSheet.createRow(i + 2);
				thirdSheet.getRow(i + 2).setHeight((short) 1000);
				row.createCell(0).setCellValue(orderDetailList.get(i).getName());
				row.createCell(1).setCellValue(orderDetailList.get(i).getAmount());
				XSSFCell cell = (XSSFCell) row.createCell(2);
				// try {
				// ExcelUtil.addimg((XSSFSheet) thirdSheet, (XSSFWorkbook) workbook,
				// orderDetailList.get(i).getImageId(), (short) cell.getColumnIndex(),
				// (short) (cell.getColumnIndex() + 1), thirdSheet.getLastRowNum(),
				// (short) thirdSheet.getLastRowNum() + 1);
				// } catch (Exception e) {
				// e.printStackTrace();
				// }
			}
		}

		return workbook;
	}

	public void createFlowerpotSheetNew(Workbook workbook, List<String> evidenceIds, String isSupplement) {
		Sheet sheet = workbook.createSheet("进场盆器");

		sheet.setMargin(XSSFSheet.BottomMargin, 0);

		sheet.setMargin(XSSFSheet.TopMargin, 0);

		List<ErpSkuDetail> list = orderSpecService.findAllFlowerpots(evidenceIds, isSupplement);

		CellRangeAddress region = null;

		Row firstRow = sheet.createRow(0);
		firstRow.setHeightInPoints(24);// 行高
		firstRow.createCell(0).setCellValue("项目名称");
		firstRow.createCell(1).setCellValue("盆器名称");
		firstRow.createCell(2).setCellValue("数量");

		XSSFCellStyle autoCellStyle = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyle.setWrapText(true);
		autoCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		autoCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		XSSFFont firstFont = (XSSFFont) workbook.createFont();
		firstFont.setFontName("黑体");
		firstFont.setFontHeightInPoints((short) 10);// 设置字体大小
		firstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		autoCellStyle.setFont(firstFont);

		XSSFCellStyle autoCellStyleNoFont = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyleNoFont.setWrapText(true);
		autoCellStyleNoFont.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		autoCellStyleNoFont.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		autoCellStyleNoFont.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderRight(HSSFCellStyle.BORDER_THIN);

		XSSFCellStyle autoCellStyleAmount12 = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyleAmount12.setWrapText(true);
		autoCellStyleAmount12.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		autoCellStyleAmount12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		autoCellStyleAmount12.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount12.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount12.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount12.setBorderRight(HSSFCellStyle.BORDER_THIN);
		XSSFFont amountFont12 = (XSSFFont) workbook.createFont();
		amountFont12.setFontName("黑体");
		amountFont12.setFontHeightInPoints((short) 12);// 设置字体大小
		amountFont12.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		autoCellStyleAmount12.setFont(amountFont12);

		firstRow.getCell(0).setCellStyle(autoCellStyle);
		firstRow.getCell(1).setCellStyle(autoCellStyle);
		firstRow.getCell(2).setCellStyle(autoCellStyle);
		sheet.setColumnWidth(0, 2743);
		sheet.setColumnWidth(1, 7095);
		sheet.setColumnWidth(2, 1207);

		Row row;
		int a = 1;
		int b = 1;
		if (CollectionUtils.isNotEmpty(list)) {
			Map<String, Set<String>> stockroomIdEidsMap = new HashMap<String, Set<String>>();

			row = sheet.createRow(1);
			row.createCell(0).setCellValue(list.get(0).getStockroomName());
			row.createCell(1).setCellValue(list.get(0).getSkuName());
			row.createCell(2).setCellValue(list.get(0).getAmount());
			row.getCell(0).setCellStyle(autoCellStyleNoFont);
			row.getCell(1).setCellStyle(autoCellStyleNoFont);
			row.getCell(2).setCellStyle(autoCellStyleAmount12);

			if (list.size() > 1) {
				for (int i = 1; i < list.size(); i++) {
					row = sheet.createRow(i + 1);
					row.createCell(0).setCellValue(list.get(i).getStockroomName());
					row.createCell(1).setCellValue(list.get(i).getSkuName());
					row.createCell(2).setCellValue(list.get(i).getAmount());
					row.getCell(0).setCellStyle(autoCellStyleNoFont);
					row.getCell(1).setCellStyle(autoCellStyleNoFont);
					row.getCell(2).setCellStyle(autoCellStyleAmount12);

					b = i + 1;
					if (!list.get(i).getStockroomId().equals(list.get(i - 1).getStockroomId())) {
						if (a != b - 1) {
							region = new CellRangeAddress(a, b - 1, 0, 0);
							sheet.addMergedRegion(region);
						}
						a = i + 1;
					}

				}
				if (a != b) {
					region = new CellRangeAddress(a, b, 0, 0);
					sheet.addMergedRegion(region);
				}
			}

		}
	}

	public static void createSecondSheetNew(Workbook workbook, List<String> evidenceIds, List<ErpSkuDetail> all) {
		Sheet sheet = workbook.createSheet("按项目");

		sheet.setMargin(XSSFSheet.BottomMargin, 0);

		sheet.setMargin(XSSFSheet.TopMargin, 0);

		XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLightOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderErp = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderSc = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderOrange = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLime = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle setBorderLemonChiffon = (XSSFCellStyle) workbook.createCellStyle();

		XSSFCellStyle autoCellStyle = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyle.setWrapText(true);
		autoCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		autoCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

		XSSFCellStyle autoCellStyleNoFont = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyleNoFont.setWrapText(true);
		autoCellStyleNoFont.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		autoCellStyleNoFont.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		autoCellStyleNoFont.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyleNoFont.setBorderRight(HSSFCellStyle.BORDER_THIN);

		XSSFCellStyle autoCellStyleAmount = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyleAmount.setWrapText(true);
		autoCellStyleAmount.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		autoCellStyleAmount.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		autoCellStyleAmount.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount.setBorderRight(HSSFCellStyle.BORDER_THIN);

		XSSFCellStyle autoCellStyleAmount12 = (XSSFCellStyle) workbook.createCellStyle(); // 自动换行
		autoCellStyleAmount12.setWrapText(true);
		autoCellStyleAmount12.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		autoCellStyleAmount12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		autoCellStyleAmount12.setBorderTop(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount12.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount12.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		autoCellStyleAmount12.setBorderRight(HSSFCellStyle.BORDER_THIN);

		setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

		setBorderErp.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderErp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderErp.setFillForegroundColor(HSSFColor.CORAL.index);

		setBorderSc.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		// setBorderSc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderSc.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

		setBorderOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderOrange.setFillForegroundColor(HSSFColor.ORANGE.index);

		// setBorderLime.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLime.setFillForegroundColor(HSSFColor.LIME.index);

		setBorderLightOrange.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLightOrange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLightOrange.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLightOrange.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

		setBorderLemonChiffon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		setBorderLemonChiffon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// setBorderLemonChiffon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// setBorderLemonChiffon.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 16);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		setBorder.setFont(font);
		setBorderErp.setFont(font);
		setBorderSc.setFont(font);

		XSSFFont firstFont = (XSSFFont) workbook.createFont();
		firstFont.setFontName("黑体");
		firstFont.setFontHeightInPoints((short) 10);// 设置字体大小
		firstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		autoCellStyle.setFont(firstFont);

		XSSFFont amountFont = (XSSFFont) workbook.createFont();
		amountFont.setFontName("黑体");
		amountFont.setFontHeightInPoints((short) 16);// 设置字体大小
		amountFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		autoCellStyleAmount.setFont(amountFont);

		XSSFFont amountFont12 = (XSSFFont) workbook.createFont();
		amountFont12.setFontName("黑体");
		amountFont12.setFontHeightInPoints((short) 12);// 设置字体大小
		amountFont12.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		autoCellStyleAmount12.setFont(amountFont12);

		CellRangeAddress region = null;

		// 第四行
		Row fourthRow = sheet.createRow(0);
		fourthRow.setHeightInPoints(24);// 行高

		fourthRow.createCell(0).setCellValue("项目名称");
		fourthRow.createCell(1).setCellValue("ERP订单商品");
		fourthRow.createCell(2).setCellValue("实际数量");
		fourthRow.createCell(3).setCellValue("ERP规格");
		fourthRow.createCell(4).setCellValue("数量");
		fourthRow.createCell(5).setCellValue("原料");
		fourthRow.createCell(6).setCellValue("数量");

		fourthRow.getCell(0).setCellStyle(autoCellStyle);
		fourthRow.getCell(1).setCellStyle(autoCellStyle);
		fourthRow.getCell(2).setCellStyle(autoCellStyle);
		fourthRow.getCell(3).setCellStyle(autoCellStyle);
		fourthRow.getCell(4).setCellStyle(autoCellStyle);
		fourthRow.getCell(5).setCellStyle(autoCellStyle);
		fourthRow.getCell(6).setCellStyle(autoCellStyle);
		sheet.setColumnWidth(0, 2743);
		sheet.setColumnWidth(1, 7095);
		sheet.setColumnWidth(2, 1207);
		sheet.setColumnWidth(3, 4279);
		sheet.setColumnWidth(4, 823);
		sheet.setColumnWidth(5, 4279);
		sheet.setColumnWidth(6, 823);

		Row row;
		Integer a = 1;
		Integer b = 1;
		Integer c = 1;
		Integer d = 1;

		// List<ErpSkuDetail> all = orderSpecService.findAll(evidenceIds);

		// productSpec-summaryId-orderAmount
		Map<String, Map<String, Map<String, Integer>>> stockroomIdProductSpecSummaryIdAmount = new HashMap<String, Map<String, Map<String, Integer>>>();

		// productSpec-summaryId-erpsku
		Map<String, Map.Entry<String, Set<ErpSpecSku>>> proSpecSummaryIdSkuMap = new HashMap<String, Map.Entry<String, Set<ErpSpecSku>>>();

		/**
		 * 以上为原始、中间数据结构
		 * 
		 * 以下为目标数据结构
		 */

		// productSpec集合
		Set<String> productSpecSet = new HashSet<>();

		// stockroom集合
		Set<String> stockroomIdSet = new HashSet<>();
		Map<String, String> stockroomIdNameMap = new HashMap<String, String>();

		Map<String, Set<String>> stockroomIdEidsMap = new HashMap<String, Set<String>>();

		// productSpec-productSpecName/picUrl
		Map<String, ErpSkuDetail> productSpecNameMap = new HashMap<String, ErpSkuDetail>();

		// stockroomId-productSpec-orderAmount
		Map<String, Map<String, Integer>> stockroomIdProductSpecAmountMap = new HashMap<String, Map<String, Integer>>();

		// productSpec-erpsku
		Map<String, Set<ErpSpecSku>> proSpecSkuMap = new HashMap<String, Set<ErpSpecSku>>();

		if (CollectionUtils.isNotEmpty(all)) {
			for (ErpSkuDetail erpSkuDetail : all) {

				addProductSpecSummaryIdAmount2(stockroomIdProductSpecSummaryIdAmount, erpSkuDetail);
				addProductSpecSkuAmount(proSpecSummaryIdSkuMap, erpSkuDetail);
				productSpecSet.add(erpSkuDetail.getProductSpecGroupKey());
				stockroomIdSet.add(erpSkuDetail.getStockroomId());
				stockroomIdNameMap.putIfAbsent(erpSkuDetail.getStockroomId(), erpSkuDetail.getStockroomName());
				productSpecNameMap.putIfAbsent(erpSkuDetail.getProductSpecGroupKey(), erpSkuDetail);

				Set<String> eidSet = stockroomIdEidsMap.get(erpSkuDetail.getStockroomId());
				if (CollectionUtils.isEmpty(eidSet)) {
					eidSet = new HashSet<String>();
				}
				eidSet.add(erpSkuDetail.getEvidenceId());
				stockroomIdEidsMap.put(erpSkuDetail.getStockroomId(), eidSet);
			}
		}

		for (String productSpecKey : productSpecSet) {
			proSpecSkuMap.put(productSpecKey, proSpecSummaryIdSkuMap.get(productSpecKey).getValue());
		}

		for (String stockroomIdKey : stockroomIdSet) {

			Map<String, Map<String, Integer>> stockProSpecSummaryIdsAmount = stockroomIdProductSpecSummaryIdAmount
					.get(stockroomIdKey);

			Map<String, Integer> productSpecAmountMap = stockroomIdProductSpecAmountMap.get(stockroomIdKey);

			if (productSpecAmountMap == null) {
				productSpecAmountMap = new HashMap<String, Integer>();
				stockroomIdProductSpecAmountMap.put(stockroomIdKey, productSpecAmountMap);
			}

			for (String productSpeckey : stockProSpecSummaryIdsAmount.keySet()) {
				Integer total = 0;
				for (Integer summaryIdAmount : stockProSpecSummaryIdsAmount.get(productSpeckey).values()) {
					total += summaryIdAmount;
				}
				productSpecAmountMap.put(productSpeckey, total);
			}

		}
		if (!stockroomIdProductSpecAmountMap.isEmpty()) {
			for (String key1 : stockroomIdProductSpecAmountMap.keySet()) {
				Map<String, Integer> map1 = stockroomIdProductSpecAmountMap.get(key1);
				if (!map1.isEmpty()) {
					for (String key2 : map1.keySet()) {
						ErpSkuDetail erpSkuDetail = productSpecNameMap.get(key2);
						Set<ErpSpecSku> erpSpecSkuSet = proSpecSkuMap.get(key2);
						if (CollectionUtils.isNotEmpty(erpSpecSkuSet)) {
							for (ErpSpecSku es : erpSpecSkuSet) {
								row = sheet.createRow(a);

								row.createCell(0);
								row.createCell(1);
								row.createCell(2);

								row.createCell(3).setCellValue(es.getSpecName());
								row.createCell(4).setCellValue(es.getSkuAmount() * es.getAmount());
								row.createCell(5).setCellValue(es.getSkuName());
								row.createCell(6).setCellValue(es.getSkuAmount() * es.getAmount());

								row.getCell(0).setCellStyle(autoCellStyleNoFont);
								row.getCell(1).setCellStyle(autoCellStyleNoFont);
								row.getCell(2).setCellStyle(autoCellStyleNoFont);
								row.getCell(3).setCellStyle(autoCellStyleNoFont);
								row.getCell(4).setCellStyle(autoCellStyleAmount);
								row.getCell(5).setCellStyle(autoCellStyleNoFont);
								row.getCell(6).setCellStyle(autoCellStyleAmount);

								a = a + 1;
							}
							c = c + erpSpecSkuSet.size();
							sheet.getRow(b).createCell(1).setCellValue(erpSkuDetail.getFullName());
							sheet.getRow(b).createCell(2).setCellValue(map1.get(key2));

							sheet.getRow(b).getCell(1).setCellStyle(autoCellStyleNoFont);
							sheet.getRow(b).getCell(2).setCellStyle(autoCellStyleAmount12);

							region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 1, 1);
							sheet.addMergedRegion(region);
							region = new CellRangeAddress(b, b + erpSpecSkuSet.size() - 1, 2, 2);
							sheet.addMergedRegion(region);

							b = b + erpSpecSkuSet.size();
						}

					}
				}

				String eids = "";
				Set<String> eidSet = stockroomIdEidsMap.get(key1);
				if (CollectionUtils.isNotEmpty(eidSet)) {
					for (String eid : eidSet) {
						eids = eids + "," + eid;
					}
				}

				sheet.getRow(d).createCell(0).setCellValue(stockroomIdNameMap.get(key1) + "\n" + eids);
				region = new CellRangeAddress(d, c - 1, 0, 0);
				sheet.addMergedRegion(region);

				sheet.getRow(d).getCell(0).setCellStyle(autoCellStyleNoFont);
				d = c;
			}
		}

	}

	/**
	 * {@inheritDoc}
	 * 
	 * @see com.z100h.supplychain.order.facade.OrderFacade#findByPage(com.jeeplus.core.persistence.Page, com.z100h.supplychain.order.entity.OrderVo)
	 * @author: Fyh - 1009173473@qq.com 
	 * @date: Created on 2022年3月1日 下午5:37:33
	 */
	@Override
	public Page<OrderVo> findByPage(Page<OrderVo> page, OrderVo orderVo) {
		return orderService.findByPage(page, orderVo);

	}

}
